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