实现数据分页查询的方案相当多,前台和后台都有很多好方法,这些好方法都有一个共同的特点:在实现分页的同时,考虑了网络资源的占有问题。本文要讨论的是使用SQL Server存储过程的实现方法。
引子
在含有ID主键(且ID连续)的Tab表中,查找第51行到第100行数据,对应的SQL语句为:
SLECET*FROME tab WHERE ID BETWEEN 51 AND 100
如果ID不连续,或者主键为其他,则可以用下SQL语句实现同样的功能:
或是
如果用变量参数控制输入行,则使用以下语句:
SELECT * FROM tab WHERE ID > @lastpage_endid
问题
对于没有主键的表,可能存在大量重复的记录,很多SQL Server使用者喜欢用下面的语句:
SELECT * FROM #T WHERE ID BETWEEN 51 AND 100
上面的方法非常笨拙,而且相当耗资源。
分析
对于这种没有主键的表,要实现分页查询,笔者认为最好的方法是加一个IDENTITY属性的主键,然后使用文本开头使用的两种方法,效率要高得多。在原表中加入IDENTITY属性的语句如下:
并不是所有用户都有修改表的权限,下面介绍一种通用的方法:使用SQL Server 提供的储存过程sp_cursoropen。具体用法如下:
exec sp_cursorfetch @P1 , 16 , @begincol , @pagesize
exec sp_cursorclose @P1
其中第一句的@P1为生成的游标ID,@sqlstr为定义游标的SLELECT字符串;第二句中@begincol为起始行数,@pagesize为输出行数;第三句sp_cursorclose意即关闭游标。
解决
以下是笔者编写的储存过程,通过传入表名,分页取出第N页数据。
( @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
调用方法
-- 表名tab ,第10页,每页100行。
进阶
以上存储过程比较通用,不过如果适当修改一下,把@sqlstr当作转入参数,就更灵活了,实现方法如下:
( @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
调用方法: