分頁查詢

--分页查询数据

--调用 sp_ShowPageQ 'TFetchData','id',0,'*',200,2,'fetchdt > ''2008-10-22'''

CREATE    PROCEDURE   sp_ShowPageQ

    @strTable       varchar(50),   --表名

    @strColumn      varchar(50),   --按该列来进行分页

    --@intColType     int,           --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型

    @intOrder       int,           --排序,0-顺序,1-倒序

    @strColumnlist  varchar(800),  --要查询出的字段列表,*表示全部字段

    @intPageSize    int,           --每页记录数

    @intPageNum     int,           --指定页

    @strWhere       varchar(800)   --查询条件

    --@intRecordCount int,           --总记录数

    --@intPageCount   int            --总页数 

 

  AS

 

  DECLARE   @sql    nvarchar(4000) --用于构造SQL语句

  DECLARE   @where1 varchar(800)   --构造条件语句

  DECLARE   @where2 varchar(800)   --构造条件语句

  DECLARE   @intRecordCount int    --总记录数

  DECLARE   @intPageCount int      --总页数

 

  IF   @strWhere   is   null   or   rtrim(@strWhere)=''

  -- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格

 

  BEGIN  --没有查询条件

      SET   @where1=' WHERE '  

      SET   @where2=' '  

  END  

  ELSE   

  BEGIN  --有查询条件 

      SET   @where1=' WHERE  ('+@strWhere+')  AND  '

      SET   @where2=' WHERE  ('+@strWhere+')  '  

  END

 

 

  set @strColumn = ' ' + @strColumn + ' '

  set @strColumnlist = ' ' + @strColumnlist + ' '

 

  --构造SQL语句，计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 )

  SET   @sql='SELECT   @intPageCount=CEILING((COUNT(*)+0.0)/'

        + CAST(@intPageSize   AS   varchar)

        + ')   FROM   ' + @strTable + @where2

 

  --执行SQL语句，计算总页数，并将其放入@intPageCount变量中

  EXEC sp_executesql  @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT

 

 

  --计算记录数 @intRecordCount

  SET   @sql='SELECT   @intRecordCount=COUNT(*) FROM   ' + @strTable + @where2

  --执行SQL语句，计算记录数，并将其放入@intRecordCount 变量中

 

  EXEC sp_executesql  @sql,N'@intRecordCount  int OUTPUT',@intRecordCount OUTPUT

 

 

  --将总页数放到查询返回记录集的第一个字段前，此语句可省略

  SET  @strColumnlist= ' '+Cast(@intPageCount as varchar(30)) + ' as PageCount,'+

                        Cast(@intRecordCount as varchar(30)) + ' as RecordCount,' + @strColumnlist  

 

  IF   @intOrder=0   --构造升序的SQL

 

      SET @sql='SELECT TOP '+ CAST(@intPageSize   AS   varchar)  +

               @strColumnlist +  

               ' FROM ' + @strTable + @where1 +

               @strColumn + '>(SELECT MAX('+@strColumn+')  '+  

               ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1)  AS  varchar) +  

               @strColumn + ' FROM '+ @strTable+@where2+'ORDER  BY '+@strColumn+')  t)  ORDER  BY '+ @strColumn  

 

  ELSE              --构造降序的SQL

 

      SET @sql='SELECT TOP '+ CAST(@intPageSize   AS   varchar)  +

               @strColumnlist+  

               ' FROM '+ @strTable + @where1 +

               @strColumn + '<(SELECT   MIN('+@strColumn+')   '+  

               ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1)  AS  varchar) +  

               @strColumn + ' FROM '+ @strTable+@where2+'ORDER   BY '+@strColumn+'   DESC)   t)   ORDER   BY   '+  

               @strColumn + ' DESC'       

 

 

  IF   @intPageNum=1--第一页

 

      SET   @sql='SELECT   TOP   '+CAST(@intPageSize   AS   varchar) + @strColumnlist + ' FROM '+@strTable+  

                 @where2+'ORDER   BY   '+@strColumn+CASE   @intOrder WHEN  0 THEN  ''  ELSE  '  DESC' 

 

 

  END  

  --PRINT   @sql  

 

  EXEC(@sql)  

 

 

 

 

GO

 

  CREATE  procedure PaginationQuery        

  @p_selectSQL   nvarchar(4000), --要执行的sql语句  

  @p_curPage   int=1 out,   --要显示的页码  

  @p_pageSize   int=20, --每页的大小  

  @p_totalRecords   int=0   out, --记录数  

  @p_totalPages   int=0   out --总页数  

  as  

  set nocount on  

  declare  

     @p1 int,

     @tmpCur int  

 

  exec sp_cursoropen @p1 output,@p_selectSQL,@scrollopt=1,@ccopt=1,@rowcount=@p_totalPages output  

  if @p_curPage<1

    set @p_curPage=1

  if @p_curPage*@p_pageSize>@p_totalPages

    set @p_curPage=ceiling(1.0*@p_totalPages/@p_pageSize)

  select @tmpCur=@p_curPage

 

  select @p_totalRecords=@p_totalPages+1,@p_totalPages=ceiling(1.0*@p_totalPages/@p_pageSize),@p_curPage=(@p_curPage-1)*@p_pageSize+1      

  exec   sp_cursorfetch   @p1,16,@p_curPage,@p_pageSize    

  select @p_curPage=@tmpCur

  exec   sp_cursorclose   @p1  

  set nocount off

 

GO