分页PROC

技术2026-01-01 1

select * from city

insert into [city](cityid,[city],father) select (cityid,[city],father) from [city]

select count(1) from area

create proc aaasbegindeclare @int intset @int=0while @int<10begininsert into area(areaid,area,father) select areaid,area,father from areaset @int=@int+1endendgo

exec aaDECLARE @RC intDECLARE @sql nvarchar(4000)DECLARE @page intDECLARE @pageSize intDECLARE @pageCount intDECLARE @recordCount intDECLARE @SearchTime int---— 设置参数值EXEC @RC = [pubs].[dbo].[p_splitpage] 'select * from area', 1000, 100, @pageCount OUTPUT , @recordCount OUTPUT , @SearchTime OUTPUT

CREATE procedure p_splitpage@sql nvarchar(4000),--要执行的sql语句@page int=1, --要显示的页码@pageSize int,--每页的大小@pageCount int=0 out,--总页数@recordCount int=0 out,--总记录数@SearchTime int=0 out --计算耗时asdeclare @usetime datetimeset @usetime=getdate()set nocount ondeclare @p1 intexec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount outputset @recordCount = @pageCountselect @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1exec sp_cursorfetch @p1,16,@page,@pagesize exec sp_cursorclose @p1set @SearchTime=datediff(ms,@usetime,getdate())print @SearchTime

DECLARE @RC intDECLARE @sql nvarchar(4000)DECLARE @page intDECLARE @pageSize intDECLARE @pageCount intDECLARE @recordCount intDECLARE @SearchTime int--— 设置参数值EXEC @RC = [pubs].[dbo].[p_splitpage] 'select * from area', 1, 100, @pageCount OUTPUT , @recordCount OUTPUT , @SearchTime OUTPUT

DECLARE @RC intDECLARE @Tables varchar(1000)DECLARE @PrimaryKey varchar(100)DECLARE @Sort varchar(200)DECLARE @CurrentPage intDECLARE @PageSize intDECLARE @Fields varchar(1000)DECLARE @Filter varchar(1000)DECLARE @Group varchar(1000)--— 设置参数值EXEC [Pages] 'companyinfo', 'c_id', 'addtime desc,c_id desc', 1093, 100, '*', '', ''

alter PROCEDURE Pages/****************************************************************** 千万数量级分页存储过程 *****************************************************************参数说明:1.Tables :表名称,视图2.PrimaryKey :主关键字3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc4.CurrentPage :当前页码5.PageSize :分页尺寸6.Filter :过滤语句，不带Where 7.Group :Group语句,不带Group By效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx***************************************************************/(@Tables varchar(1000),@PrimaryKey varchar(100),@Sort varchar(200) = NULL,@CurrentPage int = 1,@PageSize int = 10,@Fields varchar(1000) = '*',@Filter varchar(1000) = NULL,@Group varchar(1000) = NULL)AS/*默认排序*/IF @Sort IS NULL or @Sort = ''SET @Sort = @PrimaryKeyDECLARE @SortTable varchar(100)DECLARE @SortName varchar(100)DECLARE @strSortColumn varchar(200)DECLARE @operator char(2)DECLARE @type varchar(100)DECLARE @prec int/*设定排序语句.*/IF CHARINDEX('DESC',@Sort)>0BEGINSET @strSortColumn = REPLACE(@Sort, 'DESC', '')SET @operator = '<='ENDELSEBEGINIF CHARINDEX('ASC', @Sort) = 0SET @strSortColumn = REPLACE(@Sort, 'ASC', '')SET @operator = '>='ENDIF CHARINDEX('.', @strSortColumn) > 0BEGINSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))ENDELSEBEGINSET @SortTable = @TablesSET @SortName = @strSortColumnENDSelect @type=t.name, @prec=c.precFROM sysobjects o JOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertypeWhere o.name = @SortTable AND c.name = @SortNameIF CHARINDEX('char', @type) > 0SET @type = @type + '(' + CAST(@prec AS varchar) + ')'DECLARE @strPageSize varchar(50)DECLARE @strStartRow varchar(50)DECLARE @strFilter varchar(1000)DECLARE @strSimpleFilter varchar(1000)DECLARE @strGroup varchar(1000)/*默认当前页*/IF @CurrentPage < 1SET @CurrentPage = 1/*设置分页参数.*/SET @strPageSize = CAST(@PageSize AS varchar(50))SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''BEGINSET @strFilter = ' Where ' + @Filter + ' 'SET @strSimpleFilter = ' AND ' + @Filter + ' 'ENDELSEBEGINSET @strSimpleFilter = ''SET @strFilter = ''ENDIF @Group IS NOT NULL AND @Group != ''SET @strGroup = ' GROUP BY ' + @Group + ' 'ELSESET @strGroup = ''/*执行查询语句*/EXEC('DECLARE @SortColumn ' + @type + 'SET ROWCOUNT ' + @strStartRow + 'Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + 'SET ROWCOUNT ' + @strPageSize + 'Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '')GO

exec p_show 'select * from area',100,100,'*',''

CREATE Proc p_show @QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表 @PageSize int=10, --每页的大小(行数) @PageCurrent int=1, --要显示的页 @FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段 @FdOrder nvarchar (1000)='' --排序字段列表 as declare @FdName nvarchar(50) --标识列名 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号 select @FdName='[ID_'+cast(newid() as varchar(40))+']' ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20)) ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20)) ,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+' into #tb from('+@QueryStr+') a '+@FdOrder+' select '+@FdShow+' from #tb where '+@FdName+' between ' +@Id1+' and '+@Id2 ) GO

create index greadiddesc on companyinfo(GenderID desc)

select * from companyinfo where sort='397' and companyname like '%浙江黄岩%' order by addtime desc

EXEC pages 'companyinfo','c_id','*',100,1091,' ','','c_id desc, addtime desc'

create index areaiddesc on area (id desc)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------alter PROCEDURE Pages@TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名@PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空@Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *@PageSize INT, --每页记录数@CurrentPage INT, --当前页，0表示第1页@Filter VARCHAR(200) = '', --条件，可以为空，不用填 where@Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by@Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order byASBEGINDECLARE @SortColumn VARCHAR(200)DECLARE @Operator CHAR(2)DECLARE @SortTable VARCHAR(200)DECLARE @SortName VARCHAR(200)IF @Fields = ''SET @Fields = '*'IF @Filter = ''SET @Filter = 'Where 1=1'ELSESET @Filter = 'Where ' + @FilterIF @Group <>''SET @Group = 'GROUP BY ' + @Group

IF @Order <> ''BEGINDECLARE @pos1 INT, @pos2 INTSET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')IF CHARINDEX(' DESC', @Order) > 0IF CHARINDEX(' ASC', @Order) > 0BEGINIF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)SET @Operator = '<='ELSESET @Operator = '>='ENDELSESET @Operator = '<='ELSESET @Operator = '>='SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')SET @pos1 = CHARINDEX(',', @SortColumn)IF @pos1 > 0SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)SET @pos2 = CHARINDEX('.', @SortColumn)IF @pos2 > 0BEGINSET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)ELSESET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)ENDELSEBEGINSET @SortTable = @TableNamesSET @SortName = @SortColumnENDENDELSEBEGINSET @SortColumn = @PrimaryKeySET @SortTable = @TableNamesSET @SortName = @SortColumnSET @Order = @SortColumnSET @Operator = '>='END

DECLARE @type varchar(50)DECLARE @prec intSelect @type=t.name, @prec=c.precFROM sysobjects o JOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertypeWhere o.name = @SortTable AND c.name = @SortNameIF CHARINDEX('char', @type) > 0SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @TopRows INTSET @TopRows = @PageSize * @CurrentPage + 1print @TopRowsprint @Operatorexec('DECLARE @SortColumnBegin ' + @type + 'SET ROWCOUNT ' + @TopRows + 'Select @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + 'SET ROWCOUNT ' + @PageSize + 'Select ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + ' ') END

select top 10 * from companyinfo where ltrim(dealtype)='生产型'

select count(1) from companyinfo where sort='397'

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: David.Yan-- Create date: 2007.12.17-- Description: /*高效通用分页存储过程(双向检索)敬告：适用于单一主键或存在唯一值列的表或视图ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围*/-- =============================================

--------------------------------------------------------------------------------------------------------------v------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------DECLARE @RC intDECLARE @TableName varchar(200)DECLARE @FieldList varchar(2000)DECLARE @PrimaryKey varchar(100)DECLARE @Where varchar(2000)DECLARE @Order varchar(1000)DECLARE @SortType intDECLARE @RecorderCount intDECLARE @PageSize intDECLARE @PageIndex intDECLARE @TotalCount intDECLARE @TotalPageCount int--— 设置参数值EXEC @RC = [pubs].[dbo].[P_viewPage] 'area', '*', 'id', '', 'id desc', 3, 100, 1000, @TotalCount OUTPUT , @TotalPageCount OUTPUT

alter PROC P_viewPage-- Add the parameters for the stored procedure here@TableName VARCHAR(200), --表名@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*@PrimaryKey VARCHAR(100), --单一主键或唯一值键@Where VARCHAR(2000), --查询条件不含'where'字符，如id>10 and len(userid)>9@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，必须指定asc或desc--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法--@RecorderCount INT, --记录总数 0:会返回总记录@PageSize INT, --每页输出的记录数@PageIndex INT, --当前页数@TotalCount INT OUTPUT, --记返回总记录@TotalPageCount INT OUTPUT --返回总页数AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ONIF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0SET @Order = RTRIM(LTRIM(@Order))SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0BEGINSET @Order = REPLACE(@Order,', ',',')SET @Order = REPLACE(@Order,' ,',',')ENDIF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''OR ISNULL(@PrimaryKey,'') = ''OR @SortType < 1 OR @SortType >3OR @PageSize < 0 OR @PageIndex < 0--@RecorderCount < 0 OR

BEGINPRINT('ERR_00参数错误')RETURNENDIF @SortType = 3BEGINIF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')BEGINPRINT('ERR_02排序错误') RETURN ENDENDDECLARE @new_where1 VARCHAR(1000)DECLARE @new_where2 VARCHAR(1000)DECLARE @new_order1 VARCHAR(1000)DECLARE @new_order2 VARCHAR(1000)DECLARE @new_order3 VARCHAR(1000)DECLARE @Sql VARCHAR(8000)DECLARE @SqlCount NVARCHAR(4000)IF ISNULL(@where,'') = ''BEGINSET @new_where1 = ' 'SET @new_where2 = ' WHERE 'ENDELSEBEGINSET @new_where1 = ' WHERE ' + @whereSET @new_where2 = ' WHERE ' + @where + ' AND 'ENDIF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2BEGINIF @SortType = 1BEGINSET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'ENDIF @SortType = 2BEGINSET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'ENDENDELSEBEGINSET @new_order1 = ' ORDER BY ' + @OrderENDIF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0BEGINSET @new_order1 = ' ORDER BY ' + @OrderSET @new_order2 = @Order + ','SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)IF @FieldList <> '*'BEGINSET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')SET @FieldList = ',' + @FieldListWHILE CHARINDEX(',',@new_order3)>0BEGINIF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0BEGINSET @FieldList =@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))ENDSET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))ENDSET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))ENDENDSET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1--IF @RecorderCount = 0--BEGINEXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',@TotalCount OUTPUT,@TotalPageCount OUTPUT--END--ELSE--BEGIN--SELECT @TotalCount = @RecorderCount--ENDIF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)BEGINSET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)ENDIF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)BEGINIF @PageIndex = 1 --返回第一页数据BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where1 + @new_order1ENDIF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))+ ' ' + @FieldList + ' FROM '+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '+ @new_order1ENDENDELSEBEGINIF @SortType = 1 --仅主键正序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' > '+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1ENDELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'SELECT TOP ' + STR(@PageSize) + ' '+ @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' < '+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2+ ' ) AS TMP ' + @new_order1ENDENDIF @SortType = 2 --仅主键反序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' < '+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey+' FROM '+ @TableName+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1ENDELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'SELECT TOP ' + STR(@PageSize) + ' '+ @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' > '+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2+ ' ) AS TMP ' + @new_order1ENDENDIF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理BEGINIF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0BEGINPRINT('ERR_02') RETURNENDIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '+ @new_order2 + ' ) AS TMP ' + @new_order1ENDELSE --反向检索BEGINSET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '+ @new_order1 + ' ) AS TMP ' + @new_order1ENDENDENDEXEC(@Sql)GO

DECLARE @RC intDECLARE @TableName varchar(200)DECLARE @FieldList varchar(2000)DECLARE @PrimaryKey varchar(100)DECLARE @Where varchar(2000)DECLARE @Order varchar(1000)DECLARE @SortType intDECLARE @RecorderCount intDECLARE @PageSize intDECLARE @PageIndex intDECLARE @TotalCount intDECLARE @TotalPageCount int--— 设置参数值EXEC [P_viewPage_A] 'companyinfo', '*', 'c_id', ' sort=''397''', 'c_id desc', 3, 0, 100, 6, @TotalCount OUTPUT , @TotalPageCount OUTPUT print @TotalCountprint @TotalPageCount

Create PROC P_viewPage_A/*nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284敬告：适用于单一主键或存在唯一值列的表或视图ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围*/@TableName VARCHAR(200), --表名@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*@PrimaryKey VARCHAR(100), --单一主键或唯一值键@Where VARCHAR(2000), --查询条件不含'where'字符，如id>10 and len(userid)>9@Order VARCHAR(1000), --排序不含'order by'字符，如id asc,userid desc，必须指定asc或desc--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法@RecorderCount INT, --记录总数 0:会返回总记录@PageSize INT, --每页输出的记录数@PageIndex INT, --当前页数@TotalCount INT OUTPUT, --记返回总记录@TotalPageCount INT OUTPUT --返回总页数ASSET NOCOUNT ONIF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0SET @Order = RTRIM(LTRIM(@Order))SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0BEGINSET @Order = REPLACE(@Order,', ',',')SET @Order = REPLACE(@Order,' ,',',')ENDIF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''or ISNULL(@PrimaryKey,'') = ''or @SortType < 1 or @SortType >3or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0BEGINPRINT('ERR_00')RETURNENDIF @SortType = 3BEGINIF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')BEGIN PRINT('ERR_02') RETURN ENDENDDECLARE @new_where1 VARCHAR(1000)DECLARE @new_where2 VARCHAR(1000)DECLARE @new_order1 VARCHAR(1000)DECLARE @new_order2 VARCHAR(1000)DECLARE @new_order3 VARCHAR(1000)DECLARE @Sql VARCHAR(8000)DECLARE @SqlCount NVARCHAR(4000)IF ISNULL(@where,'') = ''BEGINSET @new_where1 = ' 'SET @new_where2 = ' Where 'ENDELSEBEGINSET @new_where1 = ' Where ' + @whereSET @new_where2 = ' Where ' + @where + ' AND 'ENDIF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2BEGINIF @SortType = 1BEGINSET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'ENDIF @SortType = 2BEGINSET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'ENDENDELSEBEGINSET @new_order1 = ' orDER BY ' + @OrderENDIF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0BEGINSET @new_order1 = ' orDER BY ' + @OrderSET @new_order2 = @Order + ','SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)IF @FieldList <> '*'BEGINSET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')SET @FieldList = ',' + @FieldListWHILE CHARINDEX(',',@new_order3)>0BEGINIF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0BEGINSET @FieldList =@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))ENDSET @new_order3 =SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))ENDSET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))ENDENDSET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1IF @RecorderCount = 0BEGINEXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',@TotalCount OUTPUT,@TotalPageCount OUTPUTENDELSEBEGINSelect @TotalCount = @RecorderCountENDIF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)BEGINSET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)ENDIF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)BEGINIF @PageIndex = 1 --返回第一页数据BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where1 + @new_order1ENDIF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))+ ' ' + @FieldList + ' FROM '+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '+ @new_order1ENDENDELSEBEGINIF @SortType = 1 --仅主键正序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' > '+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1ENDELSE --反向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'Select TOP ' + STR(@PageSize) + ' '+ @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' < '+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2+ ' ) AS TMP ' + @new_order1ENDENDIF @SortType = 2 --仅主键反序排序BEGINIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' < '+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey+' FROM '+ @TableName+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1ENDELSE --反向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('+ 'Select TOP ' + STR(@PageSize) + ' '+ @FieldList + ' FROM '+ @TableName + @new_where2 + @PrimaryKey + ' > '+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey+ ' FROM ' + @TableName+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2+ ' ) AS TMP ' + @new_order1ENDENDIF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理BEGINIF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0BEGIN PRINT('ERR_02') RETURN ENDIF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '+ @new_order2 + ' ) AS TMP ' + @new_order1ENDELSE --反向检索BEGINSET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '+ ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '+ @new_order1 + ' ) AS TMP ' + @new_order1ENDENDENDEXEC(@Sql)GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @RC intDECLARE @PageCurr intDECLARE @PageSize intDECLARE @QueryString varchar(5000)DECLARE @OrderString varchar(5000)DECLARE @FieldShow varchar(5000)DECLARE @RecordCount int--— 设置参数值EXEC @RC = [pubs].[dbo].[OrderUpendPager] 1000, 100, 'select * from area', 'id desc,father desc', '*', @RecordCount OUTPUT

CREATE PROCEDURE [OrderUpendPager] @PageCurr int=1, --当前页码@PageSize int=10, --每页条数@QueryString varchar(5000), --查询字符串@OrderString varchar(5000), --排序规则@FieldShow varchar(5000), --要显示的字段@RecordCount int output --总记录条数

ASdeclare @ViewName varchar(100) --临时查询视图名称set @ViewName='tempView'declare @RecordQuery varchar(5000) --记录查询declare @OrderUpend varchar(5000) --排序规则反转

if(@FieldShow='' or @FieldShow=null)set @FieldShow='*'if(@PageCurr=0 or @PageCurr=null)set @PageCurr=1if(@PageSize=0 or @PageSize=null)set @PageSize=10

if(@OrderString='' or @OrderString=null)beginprint('Err：必须设置排序规则--')returnend

--反转排序规则开始--if( charindex(',',@OrderString)=0 )beginif(charindex(' desc',@OrderString)=0)set @OrderUpend=replace(@OrderString,' asc','')+' desc' elseset @OrderUpend=replace(@OrderString,' desc','')endelsebeginset @OrderUpend=''DECLARE @strSingle varchar(100)DECLARE @strTemp varchar(1000)DECLARE @strSpace varchar(10)set @strSpace=''set @strTemp=@OrderString

while @strTemp<>''beginif(charindex(',',@strTemp)=0)beginset @strSingle=@strTempset @strTemp=''endelsebeginset @strSingle=substring(@strTemp,1,charindex(',',@strTemp)-1)set @strTemp=substring(@strTemp,charindex(',',@strTemp)+1,8000)end

if(charindex(' desc',@strSingle)=0)set @strSingle=replace(@strSingle,' asc','')+' desc' elseset @strSingle=replace(@strSingle,' desc','')print(@strSingle)set @OrderUpend=@OrderUpend+@strSpace+@strSingleset @strSpace=','

endend--反转排序规则结束--print('排序规则：'+@OrderString)print('反转后：'+@OrderUpend)

--统计记录数开始--create table #tempTBcount(myCount int) exec('insert into #tempTBcount select count(0) from ( '+@QueryString+' ) '+@ViewName+'')select @RecordCount=mycount from #tempTBcount drop table #tempTBcount--统计记录数结束--

declare @PageCount int --总页数 DECLARE @PassRecordCount int --预先取出的记录数 if(@RecordCount%@PageSize=0)set @PageCount=@RecordCount/@PageSizeelseset @PageCount=(@RecordCount-@RecordCount%@PageSize)/@PageSize+1

if (@PageCurr=1)beginset @RecordQuery='select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ('+@QueryString+') '+@ViewName+'_1 order by '+@OrderString print('只是显示第一页数据')print('实际执行的查询为：'+@RecordQuery)exec(@RecordQuery)returnend

if(@PageCurr>@PageCount)beginset @RecordQuery='select top 0 '+@FieldShow+' from('+@QueryString+') '+@ViewNameprint('总记录显示不了这么多页')print('实际执行的查询为：'+@RecordQuery)exec(@RecordQuery)returnend

if(@PageCurr * 2<=@PageCount)beginset @PassRecordCount=@PageCurr*@PageSizeset @RecordQuery=' select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ( '+'select top '+ CONVERT(nvarchar, @PageSize) +' * from ( '+'select top '+ CONVERT(nvarchar, @PassRecordCount) +' * from ( '+@QueryString+' ) '+@ViewName+'_1 order by '+@OrderString+') '+@ViewName+'_2 order by '+@OrderUpend+') '+@ViewName+'_3 order by '+@OrderString

print('头部截取')print('实际执行的查询为：'+@RecordQuery)exec(@RecordQuery)end

elsebeginset @PassRecordCount=@RecordCount-(@PageCurr-1)*@PageSizeset @RecordQuery=' select top '+CONVERT(nvarchar, @PageSize)+' '+@FieldShow+' from ( '+'select top '+ CONVERT(nvarchar, @PassRecordCount) +' * from ( '+@QueryString+' ) '+@ViewName+'_1 order by '+@OrderUpend+') '+@ViewName+'_2 order by '+@OrderStringprint('尾部截取')print('实际执行的查询为：'+@RecordQuery)exec(@RecordQuery)returnend