用存储过程实现分页查询
软件世界
编者按
这是“数据库技术”版第5次与大家见面了，本周小编给朋友们准备了一篇学习数据库技术的经验文章，希望能帮助朋友们找到学习的最佳途径。还有一篇SQL语言的高级应用文章，给SQL迷们过过瘾。“SQL Server 之恋”连载和“SQL乐园”栏目暂停一周，敬请见谅。
实现数据分页查询的方案相当多，前台和后台都有很多好方法，这些好方法都有一个共同的特点：在实现分页的同时，考虑了网络资源的占用问题。本文要讨论的是使用SQL Server存储过程的实现方法。
引子
在含有ID主键(且ID连续)的表tab中，查找第51行到第100行数据，对应的SQL语句为：
SELECT * FROM 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_endid
如果用变量参数来控制输出行数，则使用以下语句：
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, @be gincol, @pagesize
exec sp_cursorclose @P1
其中第一句中的@P1为生成的游标ID，@sqlstr为定义游标的SELECT字符串；第二句中@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(4000)
set @pagecount=(@pagecount-1)*@pagesize+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), @page count int=1, @pagesize int=99999999)
as
begin
set nocount on
declare @P1 int
set @pagecount=(@pagecount-1)*@pagesize+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