SQLServer用存储过程实现分页

实现数据分页查询的方案相当多，前台和后台都有很多好方法，这些好方法都有一个共同的特点：在实现分页的同时，考虑了网络资源的占有问题。本文要讨论的是使用SQL Server存储过程的实现方法。

 引子

在含有ID主键（且ID连续）的Tab表中，查找第51行到第100行数据，对应的SQL语句为：

SLECET*FROME tab WHERE ID BETWEEN 51 AND 100

如果ID不连续，或者主键为其他，则可以用下SQL语句实现同样的功能：

SELECT   TOP   50   *   FROM  tab  WHERE  ID  NOT   IN  ( SELECT   TOP   50  ID  FROM  tab)

或是

SELECT   TOP   50   *   FROM  tab  WHERE  ID > @lastpage_endidi

如果用变量参数控制输入行，则使用以下语句：

SET   ROWCOUNT @pagesize

SELECT   *   FROM  tab  WHERE  ID > @lastpage_endid

 问题

对于没有主键的表，可能存在大量重复的记录，很多SQL Server使用者喜欢用下面的语句：

SELECT   IDENTITY ( INT , 1 , 1 )  AS  ID, *   INTO  #T  FROM  tab

SELECT   *   FROM  #T  WHERE  ID  BETWEEN   51   AND   100

上面的方法非常笨拙，而且相当耗资源。

 分析

对于这种没有主键的表，要实现分页查询，笔者认为最好的方法是加一个IDENTITY属性的主键，然后使用文本开头使用的两种方法，效率要高得多。在原表中加入IDENTITY属性的语句如下：

ALTER   TABLE  tab  ADD  ID  INT    IDENTITY   PRIMARY   KEY

并不是所有用户都有修改表的权限，下面介绍一种通用的方法：使用SQL Server 提供的储存过程sp_cursoropen。具体用法如下：

exec  sp_cursoropen  @P1  output, @sqlstr

exec  sp_cursorfetch  @P1 , 16 , @begincol , @pagesize

exec  sp_cursorclose  @P1

其中第一句的@P1为生成的游标ID，@sqlstr为定义游标的SLELECT字符串；第二句中@begincol为起始行数，@pagesize为输出行数；第三句sp_cursorclose意即关闭游标。

 解决

以下是笔者编写的储存过程，通过传入表名，分页取出第N页数据。

Create   proc  getpage

( @tablename   varchar  ( 255 ),  @page   count   int = 1 , @pagesize   int = 99999999 ) -- @tablename为表名

as

begin

set  nocount  on

declare   @P1   int

declare   @sqlstr   nvarchar ( 400 )

set   @pagecount   = ( @pagecount - 1 ) * @pagecount + 1

set   @sqlstr = ’ select   *   from  ’ + @tablename

exec  sp_cursoropen  @P1  output, @sqlstr

exec  sp_cursorfetch  @P1 , 16 , @pagecount , @pagesize

exec  sp_cursorclose  @P1

end

调用方法

exec  getpage’tab’, 10 , 100

-- 表名tab ,第10页，每页100行。

 

 进阶

以上存储过程比较通用，不过如果适当修改一下，把@sqlstr当作转入参数，就更灵活了，实现方法如下：

Create   proc  getpage

( @sqlstr   nvarchar  ( 4000 ), @pagecont   int = 1 , @pagesize   int = 99999999 )

as

begin

set  nocount  on

declare   @P1   int

set   @pagecount   = ( @pagecount - 1 ) * @pagecount + 1

exec  sp_cursoropen  @P1  output, @sqlstr

exec  sp_cursorfetch  @P1 , 16 , @pagecount , @pagesize

exec  sp_cursorclose  @P1

end

 

调用方法:

exec  getpage’ SELECT   *   FROM  tab WHERE条件 ’, 10 , 100

转载自：http://blog.csdn.net/minisunny/article/details/1793940