多表通用的SQL存储过程分页
案例一：
USE [Community]
GO /****** Object: StoredProcedure [dbo].[Common_PageList] Script Date: 2016/3/8 13:44:58 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[Common_PageList]
(
@tab nvarchar(200),---表名
@strFld nvarchar(max), --字段字符串
@strWhere nvarchar(max), --where条件
@PageIndex int, --页码
@PageSize int, --每页容纳的记录数
@Sort nvarchar(255), --排序字段及规则,不用加order by
@Total int output --总数
)
AS
declare @strSql nvarchar(max)
set nocount on; set @strSql=' SELECT * FROM (SELECT ROW_NUMBER()
OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @strFld + ' FROM ' + @tab + ' where 1=1 ' + @strWhere + ') AS Dwhere
WHERE rownum BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' and ' + cast((@PageIndex*@PageSize) as nvarchar(20))
exec (@strSql)
--print @strSql set @strSql='SELECT @Total = COUNT(0) FROM ' + @tab + ' WHERE 1=1 ' + @strWhere
--print @strSql
EXECUTE sp_executesql @strSql,N'@Total INT OUTPUT',@Total OUTPUT set nocount off; GO
分页功能可以简化整合成统一的 SQL语句分页壳子 功能：
USE [Community]
GO /****** Object: StoredProcedure [dbo].[GetMySocialList] Script Date: 2016/3/17 15:20:18 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: 熊学浩
-- Create date: 2016-03-17
-- Description: “我的圈子"列表
-- =============================================
CREATE PROCEDURE [dbo].[GetMySocialList]
-- Add the parameters for the stored procedure here
@MemberID int, --用户ID
@PageIndex int, --页码
@PageSize int, --每页容纳的记录数
@Total int output --总数
AS
declare @strSql nvarchar(max);
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; --主查询
set @strSql='SELECT
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
SC.CheckState,
SC.Notes,
SM.CMSocialMemberID,
SM.MemberID,
SM.MemberName,
SM.MemberIntegral,
SM.EnterTime,
SM.MemberState,
SM.MemberRank,
SM.IsRecommend
FROM (SELECT * FROM CMSocialMember WHERE MemberID='+ CAST(@MemberID as nvarchar(20))+' AND IsDelete<>1 AND IsAvailable=1) AS SM
INNER JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID
LEFT JOIN (
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*
FROM CMSocialCheck WHERE IsDelete<>1
) AS SCsub WHERE SCsub.group_index=1
) AS SC ON SC.CMSocialID=SM.CMSocialID '; --WHERE S.IsAvailable = 1 print(@strSql); --拼接分页壳子
set @strSql='SELECT A.*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ASub.CMSocialID DESC) AS RowNumber,* FROM
('
+ @strSql +
' ) AS ASub
) AS A
WHERE RowNumber BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' AND ' + CAST((@PageIndex*@PageSize) as nvarchar(20));
print(@strSql);
exec (@strSql); --获取总记录数
set @strSql='SELECT @Total = COUNT(1) FROM(
(SELECT CMSocialID FROM CMSocialMember WHERE MemberID=1 AND IsDelete<>1 AND IsAvailable=1) AS SM
INNER JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID
)';
print(@strSql);
EXECUTE sp_executesql @strSql,N'@Total INT OUTPUT',@Total OUTPUT; SET NOCOUNT OFF;
END GO
也可以结合存储过程和实物：
USE [TestDB]
GO /****** Object: StoredProcedure [dbo].[GetPageDataList] Script Date: 11/16/2017 14:13:46 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO -- =============================================
-- Author: <Author,,熊学浩>
-- Create date: <Create Date,,2017/11/16>
-- Description: <Description,,Sql存储过程分页>
-- =============================================
CREATE PROC [dbo].[GetPageDataList]
(
@TableName varchar(5000), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@OrderType varchar(5000), --排序类型
@sqlWhere varchar(5000) = Null, --条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@TotalPage int output, --返回总页数
@totalRecord int output --计算总记录数 --返回总记录数 )
AS
begin Begin Tran --开始事务 Declare @sql nvarchar(500);
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1 if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where rowid between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
--print @Sql Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end GO
案例二：
USE [Community]
GO /****** Object: StoredProcedure [dbo].[Common_PageListS] Script Date: 2016/3/8 13:46:25 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[Common_PageListS]
(
@Tables nvarchar(1000), --表名/视图名
@PrimaryKey nvarchar(100), --主键
@Sort nvarchar(200) = NULL, --排序字段(不带order by)
@pageindex int = 1, --当前页码
@PageSize int = 10, --每页记录数
@Fields nvarchar(1000) = N'*', --输出字段
@Filter nvarchar(1000) = NULL, --where过滤条件(不带where)
@Group nvarchar(1000) = NULL, --Group语句(不带Group By)
@TotalCount int OUTPUT --总记录数
)
AS DECLARE @SortTable nvarchar(100)
DECLARE @SortName nvarchar(100)
DECLARE @strSortColumn nvarchar(200)
DECLARE @operator char(2)
DECLARE @type nvarchar(100)
DECLARE @prec int --设定排序语句
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END --设置排序字段类型和精度
SELECT @type=t.name, @prec=c.prec FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize nvarchar(50)
DECLARE @strStartRow nvarchar(50)
DECLARE @strFilter nvarchar(1000)
DECLARE @strSimpleFilter nvarchar(1000)
DECLARE @strGroup nvarchar(1000) IF @pageindex <1
SET @pageindex = 1
SET @strPageSize = CAST(@PageSize AS nvarchar(50))
--设置开始分页记录数
SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))
--筛选以及分组语句
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY '
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables+N' WITH(NOLOCK) ' + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--执行查询语句
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables+N' WITH(NOLOCK) ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables+N' WITH(NOLOCK) ' + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
) GO
案例三：
SQL Server 2012以上版本支持以下存储过程排序：
USE [Community]
GO /****** Object: StoredProcedure [dbo].[SP_CMSocial_QueryCMSocialShow] Script Date: 2016/10/26 15:21:28 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CMSocial_QueryCMSocialShow]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow]
GO /****** Object: StoredProcedure [dbo].[SP_CMSocial_QueryCMSocialShow] Script Date: 2016/10/26 15:21:28 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CMSocial_QueryCMSocialShow]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow] AS'
END
GO -- =============================================
-- Author: 熊学浩
-- Create date: 2016-10-26
-- Description: 圈子广场
-- =============================================
ALTER PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow]
@CMSocialCategoryID bigint=NULL,
@IndustryID bigint=NULL,
@ProvinceID bigint=NULL,
@SearchWords nvarchar(200)=NULL, @SortMode varchar(20)='CreateTime,MemberNumber', /* @SortMode='CreateTime,MemberNumber' / 'CreateTime' / 'MemberNumber' */
@StartingRowNumber bigint=0,
@FetchRows int=12,
@NeedTotal bit=0 --是否需要获取总数
AS
BEGIN
if(@CMSocialCategoryID='' or @CMSocialCategoryID<1) set @CMSocialCategoryID=null;
if(@IndustryID='' or @IndustryID<1) set @IndustryID=null;
if(@ProvinceID='' or @ProvinceID<1) set @ProvinceID=null;
if(@SearchWords is not null and @SearchWords!='' and LEN(@SearchWords)>0) set @SearchWords = '%'+@SearchWords+'%';
else set @SearchWords=null; declare @Total bigint=0; -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; select * from(
SELECT
S.CMSocialID AS 'ID',
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
S.ModifyTime,
S.CreateTime,
COUNT(DISTINCT SM.CMSocialMemberID) AS 'MemberNumber'
FROM CMSocial AS S
LEFT JOIN (SELECT * FROM CMSocialMember WHERE CMSocialMember.IsDelete<>1 AND CMSocialMember.IsAvailable=1) AS SM ON S.CMSocialID = SM.CMSocialID
WHERE S.IsAvailable=1 AND S.SocialState=0
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN(@CMSocialCategoryID)) OR @CMSocialCategoryID IS NULL)
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMIndustryRelation WHERE IndustryID IN(@IndustryID)) OR @IndustryID IS NULL)
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMAreaRelation WHERE ProvinceID IN(@ProvinceID)) OR @ProvinceID IS NULL)
AND ((S.SocialName LIKE @SearchWords OR S.SocialDescription LIKE @SearchWords) OR @SearchWords IS NULL)
GROUP BY
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
S.ModifyTime,
S.CreateTime
) as T
--ORDER BY T.CreateTime DESC,T.MemberNumber DESC
--ORDER BY MemberNumber DESC
ORDER BY /* @SortMode='CreateTime,MemberNumber' / 'CreateTime' / 'MemberNumber' 多字段，多优先级排序 */
CASE WHEN (CHARINDEX('CreateTime',@SortMode)=1) THEN (T.CreateTime) END DESC,
CASE WHEN (CHARINDEX('MemberNumber',@SortMode)=1) THEN T.MemberNumber END DESC,
CASE WHEN (CHARINDEX('CreateTime',@SortMode)>1) THEN (T.CreateTime) END DESC,
CASE WHEN (CHARINDEX('MemberNumber',@SortMode)>1) THEN T.MemberNumber END DESC OFFSET @StartingRowNumber ROWS
FETCH NEXT @FetchRows ROWS ONLY; if(@NeedTotal=1)
begin
SELECT @Total=COUNT(*)
FROM CMSocial AS S
WHERE S.IsAvailable=1 AND S.SocialState=0
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN(@CMSocialCategoryID)) OR @CMSocialCategoryID IS NULL)
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMIndustryRelation WHERE IndustryID IN(@IndustryID)) OR @IndustryID IS NULL)
AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMAreaRelation WHERE ProvinceID IN(@ProvinceID)) OR @ProvinceID IS NULL)
AND ((S.SocialName LIKE @SearchWords OR S.SocialDescription LIKE @SearchWords) OR @SearchWords IS NULL)
end
else
begin
set @Total=0;
end return @Total; SET NOCOUNT OFF;
END GO
高效的SQLSERVER分页查询的几种示例分析
http://www.cnblogs.com/xiongzaiqiren/p/sql-paging.html