原創：SQL Server的通用分頁存儲過程，未使用遊标，速度更快！

使用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）傳回總資料條數，用于用戶端進行分頁顯示

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

 1 --// ============================   

 2 --// SQL Server通用分頁存儲過程   

 3 --// Author : netwild   

 4 --// date : 2010/07/22   

 5 --// Email : [email protected]   

 6 --// QQ : 52100641（網無忌）   

 7 --// ============================   

 8    

 9  SET QUOTED_IDENTIFIER ON    

10  GO   

11  SET ANSI_NULLS ON    

12  GO   

13   

14   

15  CREATE PROC execByPage   

16   

17  @sqlQuery varchar(2000), --//輸入參數：SQL檢索語句或表名   

18   @pageSize int, --//輸入參數：每頁顯示記錄條數   

19   @pageIndex int --//輸入參數：目前頁碼   

20    

21  AS   

22   

23  SET NOCOUNT ON   

24 SET ANSI_WARNINGS OFF    

25   

26 declare @tmpTableName varchar(50)   

27 set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成随機臨時表名稱   

28   

29 declare @subIndex int   

30 set @subIndex = charindex('from',@sqlQuery)    

31 if (@subIndex > 0)   

32 begin --//帶FROM的标準檢索語句   

33     declare @sqlQuery1 varchar(2000)   

34     declare @sqlQuery2 varchar(2000)   

35     set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)   

36     set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))   

37     set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2   

38 end   

39 else --//不帶FROM的表名   

40 begin   

41     set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery   

42 end   

43 exec(@sqlQuery) --//建立并初始化臨時表資料   

44   

45 declare @indexStart varchar(20),@indexEnd varchar(20)   

46 set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//資料起始行ID   

47 set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//資料結束行ID   

48   

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

50   

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

52   

53 exec('drop table ' + @tmpTableName) --//删除臨時表   

54   

55   

56 GO   

57 SET QUOTED_IDENTIFIER OFF    

58 GO   

59 SET ANSI_NULLS ON    

60 GO   

寵辱不驚，看庭前花開花落；去留無意，望天上雲卷雲舒