分页存储过程

来源：互联网发布：sql when case 查询编辑：程序博客网时间：2024/05/16 11:38
CREATE PROCEDURE PAGE     @tblName VARCHAR(255), -- 表名    @strGetFields VARCHAR(1000)='*', -- 需要返回的列    @fldName VARCHAR(255)='', -- 排序的字段名    @PageSize INT=10, -- 页尺寸    @PageIndex INT=1, -- 页码    @doCount BIT=0, -- 返回记录总数, 非 0 值则返回    @OrderType BIT=0, -- 设置排序类型, 非 0 值则降序    @strWhere VARCHAR(1500)='' -- 查询条件 (注意: 不要加 where)AS     DECLARE @strSQL    VARCHAR(5000) -- 主语句     DECLARE @strTmp    VARCHAR(110) -- 临时变量     DECLARE @strOrder  VARCHAR(400) -- 排序类型          IF @doCount!=0     BEGIN         IF @strWhere!=''             SET @strSQL = 'select count(*) as Total from ['+@tblName+'] where '+                 @strWhere         ELSE             SET @strSQL = 'select count(*) as Total from ['+@tblName+']'     END--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况    ELSE     BEGIN         IF @OrderType!=0         BEGIN             SET @strTmp = '<(select min'              SET @strOrder = ' order by ['+@fldName+'] desc'                 --如果@OrderType不是0,就执行降序,这句很重要！        END         ELSE         BEGIN             SET @strTmp = '>(select max'             SET @strOrder = ' order by ['+@fldName+'] asc'         END          IF @PageIndex=1         BEGIN             IF @strWhere!=''                 SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+                     '    from ['+@tblName+'] where '+@strWhere+' '+@strOrder             ELSE                 SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+                     '    from ['+@tblName+'] '+@strOrder                      --如果是第一页就执行以上代码,这样会加快执行速度        END         ELSE         BEGIN             --以下代码赋予了@strSQL以真正执行的SQL代码             SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+                 '    from ['                 +@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+                 ']) from (select top '+STR((@PageIndex-1)*@PageSize)+' ['+@fldName                 +'] from ['+@tblName+']'+@strOrder+') as tblTmp)'+@strOrder                           IF @strWhere!=''                 SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+                     '    from ['                     +@tblName+'] where ['+@fldName+']'+@strTmp+'(['                     +@fldName+']) from (select top '+STR((@PageIndex-1)*@PageSize)                     +' ['                     +@fldName+'] from ['+@tblName+'] where '+@strWhere+' '                     +@strOrder+') as tblTmp) and '+@strWhere+' '+@strOrder         END     END              EXEC (@strSQL)GO    --调用：--   PAGE  '表','*','id',123456,9,0--游标查询CREATE procedure cursorPage@sqlstr   nvarchar(4000),   --查询字符串    @currentpage   int,   --第N页    @pagesize   int   --每页行数    as     set   nocount   on     declare   @P1   int,   --P1是游标的id      @rowcount   int     exec   sp_cursoropen   @P1   output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount   output     select   ceiling(1.0*@rowcount/@pagesize)   as   总页数,@rowcount   as   总行数,@currentpage   as   当前页       set   @currentpage=(@currentpage-1)*@pagesize+1     exec   sp_cursorfetch   @P1,16,@currentpage,@pagesize       exec   sp_cursorclose   @P1     set   nocount   off  GO--调用exec cursorPage 'select  * from dbo.费用表     where 销帐日期=''2010-05-16 00:00:00.000''',1,10CREATE PROC page @pagelinenum INT,--一页显示多少行                 @pagenum     INT --第几页AS   SELECT *   FROM   费用表  WHERE  费用ID   BETWEEN @pagelinenum * ( @pagenum - 1 ) + 1 AND @pagelinenum * @pagenumGO--调用page 30, 56     --2012DECLARE    @PageSize    TINYINT = 5,               @CurrentPage INT     = 1;SELECT BusinessId,CityID,ServiceShopName    FROM Business_Loginwhere IsShow=0ORDER BY OrderId    OFFSET (@PageSize * (@CurrentPage - 1)) ROWS    FETCH NEXT @PageSize ROWS ONLY;--05DECLARE @pagesize AS INT, @pagenum AS INT;SET @pagesize = 5;SET @pagenum = 1;WITH SalesRN AS(  SELECT ROW_NUMBER() OVER(ORDER BY OrderId) AS rownum,   BusinessId,CityID,ServiceShopName    FROM Business_Loginwhere IsShow=0)SELECT rownum,BusinessId,CityID,ServiceShopNameFROM SalesRNWHERE rownum > @pagesize * (@pagenum-1)  AND rownum <= @pagesize * @pagenumORDER BY rownum;
0 0