这是我之前整理的高性能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 ALTER 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

ITPub博客

高性能SQLServer通用分页存储过程

最新文章