SQL存储过程分页（通用的拼接SQL语句思路实现）
多表通用的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以上版本支持以下存储过程排序：
1 USE [Community] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[SP_CMSocial_QueryCMSocialShow] Script Date: 2016/10/26 15:21:28 ******/ 5 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CMSocial_QueryCMSocialShow]') AND type in (N'P', N'PC')) 6 DROP PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow] 7 GO 8 9 /****** Object: StoredProcedure [dbo].[SP_CMSocial_QueryCMSocialShow] Script Date: 2016/10/26 15:21:28 ******/ 10 SET ANSI_NULLS ON 11 GO 12 13 SET QUOTED_IDENTIFIER ON 14 GO 15 16 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CMSocial_QueryCMSocialShow]') AND type in (N'P', N'PC')) 17 BEGIN 18 EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow] AS' 19 END 20 GO 21 22 23 24 25 26 27 -- ============================================= 28 -- Author: 熊学浩 29 -- Create date: 2016-10-26 30 -- Description: 圈子广场 31 -- ============================================= 32 ALTER PROCEDURE [dbo].[SP_CMSocial_QueryCMSocialShow] 33 @CMSocialCategoryID bigint=NULL, 34 @IndustryID bigint=NULL, 35 @ProvinceID bigint=NULL, 36 @SearchWords nvarchar(200)=NULL, 37 38 @SortMode varchar(20)='CreateTime,MemberNumber', /* @SortMode='CreateTime,MemberNumber' / 'CreateTime' / 'MemberNumber' */ 39 @StartingRowNumber bigint=0, 40 @FetchRows int=12, 41 @NeedTotal bit=0 --是否需要获取总数 42 AS 43 BEGIN 44 if(@CMSocialCategoryID='' or @CMSocialCategoryID<1) set @CMSocialCategoryID=null; 45 if(@IndustryID='' or @IndustryID<1) set @IndustryID=null; 46 if(@ProvinceID='' or @ProvinceID<1) set @ProvinceID=null; 47 if(@SearchWords is not null and @SearchWords!='' and LEN(@SearchWords)>0) set @SearchWords = '%'+@SearchWords+'%'; 48 else set @SearchWords=null; 49 50 declare @Total bigint=0; 51 52 -- SET NOCOUNT ON added to prevent extra result sets from 53 -- interfering with SELECT statements. 54 SET NOCOUNT ON; 55 56 select * from( 57 SELECT 58 S.CMSocialID AS 'ID', 59 S.CMSocialID, 60 S.SocialName, 61 S.SocialDescription, 62 S.SocialLogo, 63 S.SocialAuthority, 64 S.Integral, 65 S.SocialState, 66 S.IsAvailable, 67 S.ModifyTime, 68 S.CreateTime, 69 COUNT(DISTINCT SM.CMSocialMemberID) AS 'MemberNumber' 70 FROM CMSocial AS S 71 LEFT JOIN (SELECT * FROM CMSocialMember WHERE CMSocialMember.IsDelete<>1 AND CMSocialMember.IsAvailable=1) AS SM ON S.CMSocialID = SM.CMSocialID 72 WHERE S.IsAvailable=1 AND S.SocialState=0 73 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN(@CMSocialCategoryID)) OR @CMSocialCategoryID IS NULL) 74 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMIndustryRelation WHERE IndustryID IN(@IndustryID)) OR @IndustryID IS NULL) 75 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMAreaRelation WHERE ProvinceID IN(@ProvinceID)) OR @ProvinceID IS NULL) 76 AND ((S.SocialName LIKE @SearchWords OR S.SocialDescription LIKE @SearchWords) OR @SearchWords IS NULL) 77 GROUP BY 78 S.CMSocialID, 79 S.SocialName, 80 S.SocialDescription, 81 S.SocialLogo, 82 S.SocialAuthority, 83 S.Integral, 84 S.SocialState, 85 S.IsAvailable, 86 S.ModifyTime, 87 S.CreateTime 88 ) as T 89 --ORDER BY T.CreateTime DESC,T.MemberNumber DESC 90 --ORDER BY MemberNumber DESC 91 ORDER BY /* @SortMode='CreateTime,MemberNumber' / 'CreateTime' / 'MemberNumber' 多字段，多优先级排序 */ 92 CASE WHEN (CHARINDEX('CreateTime',@SortMode)=1) THEN (T.CreateTime) END DESC, 93 CASE WHEN (CHARINDEX('MemberNumber',@SortMode)=1) THEN T.MemberNumber END DESC, 94 CASE WHEN (CHARINDEX('CreateTime',@SortMode)>1) THEN (T.CreateTime) END DESC, 95 CASE WHEN (CHARINDEX('MemberNumber',@SortMode)>1) THEN T.MemberNumber END DESC 96 97 OFFSET @StartingRowNumber ROWS 98 FETCH NEXT @FetchRows ROWS ONLY; 99 100 if(@NeedTotal=1) 101 begin 102 SELECT @Total=COUNT(*) 103 FROM CMSocial AS S 104 WHERE S.IsAvailable=1 AND S.SocialState=0 105 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMSocialCategoryRelation WHERE CMSocialCategoryID IN(@CMSocialCategoryID)) OR @CMSocialCategoryID IS NULL) 106 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMIndustryRelation WHERE IndustryID IN(@IndustryID)) OR @IndustryID IS NULL) 107 AND (S.CMSocialID IN(SELECT DISTINCT CMSocialID FROM CMAreaRelation WHERE ProvinceID IN(@ProvinceID)) OR @ProvinceID IS NULL) 108 AND ((S.SocialName LIKE @SearchWords OR S.SocialDescription LIKE @SearchWords) OR @SearchWords IS NULL) 109 end 110 else 111 begin 112 set @Total=0; 113 end 114 115 return @Total; 116 117 SET NOCOUNT OFF; 118 END 119 120 121 122 123 124 125 GO
高效的SQLSERVER分页查询的几种示例分析
http://www.cnblogs.com/xiongzaiqiren/p/sql-paging.html
