SQL Server的通用分頁預存程序 未使用遊標，速度更快！

正常情況下，SQL Server伺服器上會對使用頻率大的Table建立合適的索引
這樣能大幅度的提高資料庫本身的資料檢索速度，建立索引的方法就不細說了

如果需要返回大量資料，從幾百行到幾萬行，甚至幾十萬行資料
這時會發現響應速度越來越慢，甚至發生響應逾時的錯誤
為瞭解決這種大資料量請求的問題，就不得不使用分頁模式了

在這方面，JDBC就強悍得多，它可以將指定的行數和SQL請求一併發送給SQL Server，這樣只返回分頁後的資料，JDBC的原理還不清楚，但在實際使用中，速度還是非常快的

如果沒辦法使用JDBC，最常用的方法就是預存程序了！

我在寫這個分頁儲存之前，參考了網上的大量相關文章，可以通過關鍵字：SQL Server 分頁 進行搜尋
他們主要都是利用SQL中的Top方法，並且對所檢索的資料結構要求有識別欄位，如果沒有識別欄位，或者是聯合主鍵，那麼就會非常麻煩了。而且對應用裡原有的SQL檢索部分需要修改的地方較多，工作量較大。

因此，我在寫這個儲存之前就要求一定要對原有的SQL指令碼最大程度的相容

經過一個下午的時間，和我一個同事（絕對是高手）的共同努力下，摸索出了以下的思路：

1、確定儲存的輸入參數：
1）SQL指令碼，該參數接收完整的、正確的SQL檢索文本，可將原應用中寫好的SQL指令碼直接傳入
2）每頁的資料容量，就是一頁有多少條資料
3）當前頁碼
2、確定分頁機制：
1）執行傳入的SQL指令碼，並將結果產生暫存資料表
2）修改暫存資料表的結構，增加識別欄位欄位
3）根據識別欄位欄位，計算出指定頁碼內的記錄範圍，並返回
4）返回總資料條數，用於用戶端進行分頁顯示

根據以上的思路，編寫出以下通用的分頁預存程序：

複製代碼 代碼如下:[code]
--// ============================
--// SQL Server通用分頁預存程序
--// Author : netwild
--// date : 2010/07/22
--// Email : netwild@163.com
--// QQ : 52100641（網無忌）
--// ============================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC execByPage

@sqlQuery varchar(2000), --//輸入參數：SQL檢索語句或表名
@pageSize int, --//輸入參數：每頁顯示記錄條數
@pageIndex int --//輸入參數：當前頁碼

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

declare @tmpTableName varchar(50)
set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//產生隨機暫存資料表名稱

declare @subIndex int
set @subIndex = charindex('from',@sqlQuery)
if (@subIndex > 0)
begin --//帶FROM的標準檢索語句
declare @sqlQuery1 varchar(2000)
declare @sqlQuery2 varchar(2000)
set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)
set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))
set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2
end
else --//不帶FROM的表名
begin
set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery
end
exec(@sqlQuery) --//建立並初始化暫存資料表資料

declare @indexStart varchar(20),@indexEnd varchar(20)
set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//資料起始行ID
set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//資料結束行ID

exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//檢索該頁資料

exec('select max(ID1516) as recordCount from ' + @tmpTableName) --//提取總條數

exec('drop table ' + @tmpTableName) --//刪除暫存資料表

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/code]