高性能SQLServer通用分页存储过程
这是我之前整理的高性能SQLServer 通用分页存储过程，测试性能还不错，特此分享出来，如果有人能更好地优化，请留言，谢谢！
SQL代码
1 USE [数据库名称]
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'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
18 @OrderType TINYINT, --排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
19 @PageIndex INT, --当前页数
20 @PageSize INT, --每页输出的记录数
21 @RecorderCount INT = 0 --记录总数，如果小于等于0则重新统计总数
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 --重新统计总记录数
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
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'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
18 @OrderType TINYINT, --排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
19 @PageIndex INT, --当前页数
20 @PageSize INT, --每页输出的记录数
21 @RecorderCount INT = 0 --记录总数，如果小于等于0则重新统计总数
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 --重新统计总记录数
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
