sqlserver 資料庫預存程序分頁

1. SET QUOTED_IDENTIFIER OFF 
2. GO
3. SET ANSI_NULLS OFF 
4. GO
6. ALTER  PROCEDURE CMS_SearchGetDataByPage 
7.     @tblName      varchar(255),       -- 表名 
8.     @fldNames      varchar(1000),     -- 選擇的欄位列表以,分隔 
9.     @selectOrderFldName      varchar(500),  -- 排序欄位以,分隔（不能含keyFldName指定的欄位，可為空白） 
10.     @orderFldDesc      varchar(500),  -- 排序欄位及排序方向，如addDate desc,id desc（排序欄位需通過selectOrderFldName指定，可為空白） 
11.     @keyFldName      varchar(255),     -- 主鍵欄位 
12.     @PageSize     int = 10,           -- 頁尺寸 
13.     @PageIndex    int = 1,            -- 頁碼 
14.     @strWhere     varchar(1000) = ''  -- 查詢條件(注意: 不要加where) 
15. AS 
16. declare @strWhereA varchar(1200)            -- 臨時變數，給sqlwhere加where 
17. declare @strOrderA varchar(2000)            -- 第一次排序類型 
18. declare @strOrderB varchar(2000)            -- 第二次排序類型 
19. declare @strSqlA varchar(4000)          -- 第一次選出 
20. declare @strSqlB varchar(8000)          -- 第二次選出 
21. declare @strSQL varchar(8000)           -- 最後選出 
22. /* 條件*/ 
23. if @strWhere != '' 
24.     set @strWhereA = ' where ' + @strWhere 
25. else 
26.     set @strWhereA = '' 
27.      
28. /* 選擇欄位列表*/ 
29. if @fldNames is null or rtrim(@fldNames) = '' 
30.     set @fldNames = '*' 
31.      
32. /* 排序欄位列表*/ 
33. if not(@selectOrderFldName is null or rtrim(@selectOrderFldName) = '') 
34.     if rtrim(@selectOrderFldName) = 'id' 
35.         set @selectOrderFldName = '' 
36.     else 
37.         set @selectOrderFldName = ',' + @selectOrderFldName 
38.      
39. /* 構建order，按指定方式排序*/ 
40. if @orderFldDesc is null or rtrim(@orderFldDesc) = '' 
41.     set @orderFldDesc = ' order by id desc' 
42. else 
43.     set @orderFldDesc = ' order by ' + @orderFldDesc 
44. set @strOrderA = UPPER(@orderFldDesc) 
45. set @strOrderB =  replace(@strOrderA,'DESC','DESC1') 
46. set @strOrderB =  replace(@strOrderB,'ASC','DESC') 
47. set @strOrderB =  replace(@strOrderB,'DESC1','ASC') 
49. /* 第一頁*/ 
50. if @PageIndex = 1 
51.     set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldNames + ' from [' + @tblName + '] with(nolock)' + @strWhereA + ' ' + @strOrderA 
52. else 
53.     begin 
54.         --取得總記錄數 
55.         declare @sql nvarchar(500) 
56.         declare @maxCount int 
57.         declare @maxPage int 
58.         declare @tempRowCount int 
59.         set @sql ='select @maxCount = count('+@keyFldName+') from [' + @tblName + ']' + @strWhereA 
60.         exec sp_executesql @sql,N'@maxCount int output',@maxCount output 
61.         set @maxPage = @maxCount / @PageSize 
62.         if(@maxCount % @PageSize > 0) 
63.             set @maxPage = @maxPage + 1 
64.         /* 最後一頁*/ 
65.         if @PageIndex >= @maxPage 
66.             begin 
67.                 set @PageIndex = @maxPage 
68.                 set @strSqlA = char(13) + '(select top '+str(@maxCount % @PageSize)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + '  )' + char(13) 
69.                 set @strSqlB = char(13) + '(select ' + @keyFldName +' from ' + @strSqlA  + ' as b )' + char(13) 
70.                 set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA + char(13) 
71.             end 
72.         else 
73.             begin 
74.                 /* 不是第一頁，也不是最後一頁*/ 
75.                 if(@PageIndex <= @maxPage / 2) 
76.                     begin 
77.                     --前半數的頁 
78.                         set @tempRowCount = @PageIndex * @PageSize 
79.                         /* 構建SQL，本分頁演算法的目的是為了實現高效的非主鍵排序的分頁。by tony */ 
80.                         /* 1、先按指定欄位+主鍵欄位按降序選出perPage*pageNum條記錄*/ 
81.                         set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a  with(nolock) ' + @strWhereA + @strOrderA + '  )' + char(13) 
82.                         /* 2、再從選出的記錄中按升序選出perPage條記錄*/ 
83.                         set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA  + ' as b ' + @strOrderB + '  )' + char(13) 
84.                         /* 3、從資料庫中選出主鍵在第二次選出的記錄中的記錄，按降序排列，分頁完成*/ 
85.                         set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA 
86.                     end 
87.                 else 
88.                     begin 
89.                     --後半數的頁 
90.                         set @tempRowCount = @maxCount - (@PageIndex -1) * @PageSize 
91.                         /* 構建SQL，本分頁演算法的目的是為了實現高效的非主鍵排序的分頁。by tony */ 
92.                         /* 1、先按指定欄位+主鍵欄位按降序選出perPage*pageNum條記錄*/ 
93.                         set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a  with(nolock) ' + @strWhereA + @strOrderB + '  )' + char(13) 
94.                         /* 2、再從選出的記錄中按升序選出perPage條記錄*/ 
95.                         set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA  + ' as b ' + @strOrderA + '  )' + char(13) 
96.                         /* 3、從資料庫中選出主鍵在第二次選出的記錄中的記錄，按降序排列，分頁完成*/ 
97.                         set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA 
98.                     end 
99.                 end 
100.     end 
102. set nocount on 
104. /*print @strSQL*/ --顯示SQL 
105. exec (@strSQL) 
106. set nocount off 
107. RETURN 
108. GO
109. SET QUOTED_IDENTIFIER OFF 
110. GO
111. SET ANSI_NULLS ON 
112. GO