?

這是我之前整理的高性能SQLServer 通用分頁存儲過程，測試性能還不錯，特此分享出來，如果有人能更好地優(yōu)化，請留言，謝謝！

SQL代碼 ?1?USE?[數(shù)據(jù)庫名稱]
?2?GO
?3?/******?Object:??StoredProcedure?[dbo].[dbTab_PagerHelper]????Script?Date:?08/22/2010?13:30:39?******/
?4?SET?ANSI_NULLS?ON
?5?GO
?6?SET?QUOTED_IDENTIFIER?ON
?7?GO
?8?--?=============================================
?9?--?Author:????Jarry
10?--?Create?date:?2009-08-01
11?--?Description:????通用分頁存儲過程
12?--?=============================================
13?CREATE?PROCEDURE?[dbo].[dbTab_PagerHelper]?
14?????@TableName?VARCHAR(50),?--表名
15?????@FieldNames?VARCHAR(1000),?--顯示列名，如果是全部字段則為*
16?????@WhereString?VARCHAR(256)?=?NULL,?--查詢條件?不含'WHERE'字符，如[id]>5?AND?[userid]>10000
17?????@OrderField?VARCHAR(256)?=?NULL,?--排序不含'ORDER?BY'字符，當(dāng)@SortType=3時(shí)生效，必須指定ASC或DESC，建議在最后加上主鍵
18?????@OrderType?TINYINT,?--排序規(guī)則（1:單列正序ASC；2:單列倒序DESC；3:多列排序；）
19?????@PageIndex?INT,?--當(dāng)前頁數(shù)
20?????@PageSize?INT,?--每頁輸出的記錄數(shù)
21?????@RecorderCount?INT?=?0?--記錄總數(shù)，如果小于等于0則重新統(tǒng)計(jì)總數(shù)
22?AS
23?BEGIN
24?????SET?NOCOUNT?ON;
25?????DECLARE?@LowerBound?int,?@UpperBound?int;
26?????SET?@LowerBound?=?(@PageSize?*?(@PageIndex?-?1));
27?????SET?@UpperBound?=?(@LowerBound?+?@PageSize?-?1);
28?????DECLARE?@MSSQL?NVARCHAR(3000),?@Where?NVARCHAR(500),?@Order?VARCHAR(256),?@Order2?VARCHAR(256);
29?????SET?@MSSQL?=?'';
30?????SET?@Where?=?'';
31?????SET?@Order?=?'';
32?????SET?@Order2?=?'';
33?????IF((@WhereString?IS?NOT?NULL)?AND?(@WhereString?!=?''))?
34?????????SET?@Where?=?'?WHERE?'?+?@WhereString;
35?????IF((@OrderType?IS?NOT?NULL)?AND?(@OrderType?>?0))
36?????BEGIN
37?????????SET?@Order?=?'?ORDER?BY?'?+?@OrderField;
38?????????IF(@OrderType?=?1)
39?????????????SET?@Order?=?@Order?+?'?ASC';
40?????????ELSE?IF(@OrderType?=?2)
41?????????????SET?@Order?=?@Order?+?'?DESC';
42?????????SET?@Order2?=?REPLACE(REPLACE(UPPER(@Order),?'?ASC',?'?{ASC}'),?'?DESC',?'?{DESC}');
43?????????SET?@Order2?=?REPLACE(REPLACE(UPPER(@Order2),?'?{ASC}',?'?DESC'),?'?{DESC}',?'?ASC');
44?????END
45?????
46?????--重新統(tǒng)計(jì)總記錄數(shù)
47?????IF(@RecorderCount?<=?0)
48?????????EXECUTE('SELECT?COUNT(*)?AS?[RecorderCount]?FROM?'?+?@TableName?+?@Where);
49?????ELSE
50?????????SELECT?@RecorderCount?AS?[RecorderCount];
51?????
52?????IF(@PageIndex?<=?1)?--如果是第一頁
53?????BEGIN
54?????????SET?@MSSQL?=?'SELECT?TOP?'?+?STR(@PageSize)?+'?'+?@FieldNames?+?'?FROM?'?+?@TableName?+?@Where?+?@Order;
55?????END
56?????ELSE?IF((@PageSize*@PageIndex)?>=?@RecorderCount)?--如果是最后一頁
57?????BEGIN
58?????????SET?@MSSQL?=?'SELECT?'?+?@FieldNames?+?'?FROM?(SELECT?TOP?'?+?STR(@PageSize?-?((@PageSize*@PageIndex)?-?@RecorderCount))?+?'?'?+?@FieldNames?+?'?FROM?'?+?@TableName?+?@Where?+?@Order2?+?')?AS?[PagerTempTable]'?+?@Order;
59?????END
60?????ELSE
61?????BEGIN
62?????????IF(@OrderType?<?3)?--單列排序分頁方法
63?????????BEGIN
64?????????????SET?@MSSQL?=?'SELECT?TOP?'?+?STR(@PageSize);
65?????????????SET?@MSSQL?=?@MSSQL?+?'?'?+?@FieldNames;
66?????????????SET?@MSSQL?=?@MSSQL?+?'?FROM?'?+?@TableName;
67?????????????SET?@MSSQL?=?@MSSQL?+?@Where;
68?????????????DECLARE?@TempStrings?NVARCHAR(500);
69?????????????SET?@TempStrings?=?'';
70?????????????IF(@OrderType?>?0)
71?????????????BEGIN
72?????????????????IF(@Where?<>?'')
73?????????????????????SET?@TempStrings?=?@TempStrings?+?'?AND';
74?????????????????ELSE
75?????????????????????SET?@TempStrings?=?'?WHERE?';
76?????????????????SET?@TempStrings?=?@TempStrings?+?'?'?+?@OrderField;
77?????????????????
78?????????????????IF(@OrderType?=?1)
79?????????????????BEGIN
80?????????????????????SET?@TempStrings?=?@TempStrings?+?'?>?(SELECT?MAX';
81?????????????????END
82?????????????????ELSE
83?????????????????BEGIN
84?????????????????????SET?@TempStrings?=?@TempStrings?+?'?<?(SELECT?MIN';
85?????????????????END
86?????????????????SET?@TempStrings?=?@TempStrings?+?'('?+?@OrderField?+?')?FROM?(SELECT?TOP?'+STR(@LowerBound)+'?'?+?@OrderField?+?'?FROM?'?+?@TableName?+?@Where?+?@Order;
87?????????????????SET?@TempStrings?=?@TempStrings?+?')?AS?[PagerTempTable])';
88?????????????????
89?????????????END
90?????????????SET?@MSSQL?=?@MSSQL?+?@TempStrings?+?@Order;
91?????????END
92?????????ELSE?--多列排序分頁方法
93?????????BEGIN
94?????????????SET?@MSSQL?=?'SELECT?TOP?'?+?STR(@PageSize)?+?'?'?+?@FieldNames?+?'?FROM?(SELECT?TOP?'?+?STR(@PageSize)?+?'?'?+?@FieldNames?+?'?FROM?(SELECT?TOP?'?+?STR(@PageSize*@PageIndex)?+?'?'?+?@FieldNames?+?'?FROM?'?+?@TableName?+?@Where?+?@Order?+?')?AS?[PagerTempTable]'?+?@Order2?+?')?AS?[PagerTempTable]'?+?@Order?+?';'
95?????????END
96?????END
97?????EXECUTE(@MSSQL);
98?END

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/hijarry/archive/2010/08/22/1805809.html

總結(jié)

以上是生活随笔為你收集整理的高性能SQLServer通用分页存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。