分页存储过程整理

一、TOP n 实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView @tbname     sysname,               --要分页显示的表名 @FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段 @PageCurrent int=1,               --要显示的页码 @PageSize   int=10,                --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='',      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC                                           用于指定排序顺序 @Where    nvarchar(1000)='',     --查询条件 @PageCount int OUTPUT             --总页数 AS SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN     RAISERROR(N'对象"%s"不存在',1,16,@tbname)     RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0     AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0     AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN     RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)     RETURN END

--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN     RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)     RETURN END

--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N''     SET @FieldOrder=N'' ELSE     SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N''     SET @Where=N'' ELSE     SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN     DECLARE @sql nvarchar(4000)     SET @sql=N'SELECT @PageCount=COUNT(*)'         +N' FROM '+@tbname         +N' '+@Where     EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT     SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END

--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize,     @TopN1=(@PageCurrent-1)*@PageSize

--第一页直接显示 IF @PageCurrent=1     EXEC(N'SELECT TOP '+@TopN         +N' '+@FieldShow         +N' FROM '+@tbname         +N' '+@Where         +N' '+@FieldOrder) ELSE BEGIN     --处理别名     IF @FieldShow=N'*'         SET @FieldShow=N'a.*'

    --生成主键(惟一键)处理条件     DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),         @s nvarchar(1000),@Field sysname     SELECT @Where1=N'',@Where2=N'',@s=@FieldKey     WHILE CHARINDEX(N',',@s)>0         SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),             @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),             @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,             @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',             @Where=REPLACE(@Where,@Field,N'a.'+@Field),             @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),             @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)     SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),         @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),         @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),         @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),            @Where2=CASE             WHEN @Where='' THEN N'WHERE ('             ELSE @Where+N' AND ('             END+N'b.'+@s+N' IS NULL'+@Where2+N')'     --执行查询     EXEC(N'SELECT TOP '+@TopN         +N' '+@FieldShow         +N' FROM '+@tbname         +N' a LEFT JOIN(SELECT TOP '+@TopN1         +N' '+@FieldKey         +N' FROM '+@tbname         +N' a '+@Where         +N' '+@FieldOrder         +N')b ON '+@Where1         +N' '+@Where2         +N' '+@FieldOrder) END

 

二、字符串缓存实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView @tbname     sysname,               --要分页显示的表名 @FieldKey   sysname,               --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1,                 --要显示的页码 @PageSize   int=10,                --每页的大小(记录数) @FieldShow  nvarchar(1000)='',      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder  nvarchar(1000)='',     --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC                                           用于指定排序顺序 @Where     nvarchar(1000)='',      --查询条件 @PageCount  int OUTPUT            --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN     RAISERROR(N'对象"%s"不存在',1,16,@tbname)     RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0     AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0     AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN     RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)     RETURN END

--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN     RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)     RETURN END

--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N''     SET @FieldOrder=N'' ELSE     SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N''     SET @Where=N'' ELSE     SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN     SET @sql=N'SELECT @PageCount=COUNT(*)'         +N' FROM '+@tbname         +N' '+@Where     EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT     SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END

--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize,     @TopN1=@PageCurrent*@PageSize   

--第一页直接显示 IF @PageCurrent=1     EXEC(N'SELECT TOP '+@TopN         +N' '+@FieldShow         +N' FROM '+@tbname         +N' '+@Where         +N' '+@FieldOrder) ELSE BEGIN     SELECT @PageCurrent=@TopN1,         @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN             +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey             +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname             +N' '+@Where             +N' '+@FieldOrder     SET ROWCOUNT @PageCurrent     EXEC sp_executesql @sql,         N'@n int,@s nvarchar(4000) OUTPUT',         @PageCurrent,@sql OUTPUT     SET ROWCOUNT 0     IF @sql=N''         EXEC(N'SELECT TOP 0'             +N' '+@FieldShow             +N' FROM '+@tbname)     ELSE     BEGIN         SET @sql=STUFF(@sql,1,1,N'')                --执行查询         EXEC(N'SELECT TOP '+@TopN             +N' '+@FieldShow             +N' FROM '+@tbname             +N' WHERE '+@FieldKey             +N' IN('+@sql             +N') '+@FieldOrder)     END END

三、使用系统存储过程实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView   @sql         ntext,     --要执行的sql语句 @PageCurrent int=1,     --要显示的页码 @PageSize    int=10,    --每页的大小 @PageCount   int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen     @cursor=@p1 OUTPUT,     @stmt=@sql,     @scrollopt=1,     @ccopt=1,     @rowcount=@PageCount OUTPUT

--计算总页数 IF ISNULL(@PageSize,0)<1     SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount     SET @PageCurrent=1 ELSE     SET @PageCurrent=(@PageCurrent-1)*@PageSize+1

--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize

--关闭分页游标 EXEC sp_cursorclose @p1

四、SQL 2005的ROW_NUMBER()实现分页功能

DECLARE @pagenum AS INT, @pagesize AS INT SET @pagenum = 2 SET @pagesize = 3 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,         newsid, topic, ntime, hits       FROM news) AS D WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize ORDER BY newsid DESC

五、使用内存表

create proc Proc_paged (     @pagesize int,     @pagenum int,     @pagecount int output ) as begin --声明变量 declare @tmptable table(id int identity (1,1),userid nchar(5)) declare @idBengin int declare @idend int --构造内存表 insert into @tmptable (userid )(select userid from users) select @pagecount=count(*) from @tmptable if(@pagecount%@pagesize>0) set @pagecount=@pagecount/@pagesize+1 else set @pagecount=@pagecount/@pagesize set @idBengin=(@pagenum-1)*@pagesize set @idend=@idBengin+@pagesize select t2.id,t1.* from users t1,@tmptable t2 where t1.userid=t2.userid and      t2.id>@idBengin and t2.id< end

 

六、SQL 2005 版本 通用分页存储过程

-- ============================================= -- Author:  黄剑平 -- Create date: 2007-5-11 -- Description: SQL 2005 版本 通用分页存储过程 -- BLOG:  http://www.fnsword.com -- ============================================= Create PROCEDURE [dbo].[Pagination] @Page int = 1,      -- 当前页码 @PageSize int = 10,     -- 每页记录条数(页面大小) @Table nvarchar(500),    -- 表名或视图名，甚至可以是嵌套SQL：(Select * From Tab Where ID>1000) Tab @Field nvarchar(200) = '*',   -- 返回记录集字段名，","隔开，默认是"*" @OrderBy nvarchar(100) = 'ID ASC', -- 排序规则 @Filter nvarchar(500),    -- 过滤条件 @MaxPage smallint output,   -- 执行结果 -1 error, 0 false, maxpage true @TotalRow int output,    -- 记录总数 /* 2007-07-12 22:11:00 update */ @Descript varchar(100) output  -- 结果描述 AS BEGIN Set ROWCOUNT @PageSize;

Set @Descript = 'successful'; -------------------参数检测---------------- IF LEN(RTRIM(LTRIM(@Table))) !> 0 Begin   Set @MaxPage = 0;   Set @Descript = 'table name is empty';   Return; End

IF LEN(RTRIM(LTRIM(@OrderBy))) !> 0 Begin   Set @MaxPage = 0;   Set @Descript = 'order is empty';   Return; End

IF ISNULL(@PageSize,0) <= 0 Begin   Set @MaxPage = 0;   Set @Descript = 'page size error';   Return; End

IF ISNULL(@Page,0) <= 0 Begin   Set @MaxPage = 0;   Set @Descript = 'page error';   Return; End -------------------检测结束----------------

Begin Try   -- 整合SQL   Declare @SQL nvarchar(4000), @Portion nvarchar(4000);

  Set @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM FROM ' + @Table;

  Set @Portion = @Portion + (CASE WHEN LEN(@Filter) >= 1 THEN (' Where ' + @Filter + ') AS tab') ELSE (') AS tab') END);

Set @SQL = 'Select TOP(' + CAST(@PageSize AS nvarchar(8)) + ') ' + @Field + ' FROM (Select ' + @Field + ',' + @Portion;

  Set @SQL = @SQL + ' Where tab.ROWNUM > ' + CAST((@Page-1)*@PageSize AS nvarchar(8));

  -- 执行SQL, 取当前页记录集   Execute(@SQL);   --------------------------------------------------------------------

  -- 整合SQL   Set @SQL = 'Set @Rows = (Select MAX(ROWNUM) FROM (Select' + @Portion + ')';

  -- 执行SQL, 取最大页码   Execute sp_executesql @SQL, N'@Rows int output', @TotalRow output;   Set @MaxPage = (CASE WHEN (@TotalRow % @PageSize)<>0 THEN (@TotalRow / @PageSize + 1) ELSE (@TotalRow / @PageSize) END); End Try Begin Catch   -- 捕捉错误   Set @MaxPage = -1;   Set @Descript = 'error line: ' + CAST(ERROR_LINE() AS varchar(8)) + ', error number: ' + CAST(ERROR_NUMBER() AS varchar(8)) + ', error message: ' + ERROR_MESSAGE();   Return; End Catch;

-- 执行成功 Return; END