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

USE [数据库名称]
GO
/****** Object:  StoredProcedure [dbo].[sp_PagerHelper]    Script Date: 08/31/2010 14:47:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[dbTab_PagerHelper] 
    @TableName VARCHAR(50),-- 表名
    @FieldNames VARCHAR(1000),-- 显示列名，如果是全部字段则为*
    @WhereString VARCHAR(256) = NULL,-- 查询条件 不含'WHERE'字符，如[id]>5 AND [userid]>10000
    @OrderField VARCHAR(256) = NULL,-- 排序不含'ORDER BY'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
    @OrderType TINYINT,-- 排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
    @PageIndex INT,-- 当前页数
    @PageSize INT,-- 每页输出的记录数
    @RecorderCount INT = 0 -- 记录总数，如果小于等于0则重新统计总数
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @LowerBound int,@UpperBound int;
    SET @LowerBound = (@PageSize * (@PageIndex - 1));
    SET @UpperBound = (@LowerBound + @PageSize - 1);
    DECLARE @MSSQL NVARCHAR(3000),@Where NVARCHAR(500),@Order VARCHAR(256),@Order2 VARCHAR(256);
    SET @MSSQL = '';
    SET @Where = '';
    SET @Order = '';
    SET @Order2 = '';
    IF((@WhereString IS NOT NULL) AND (@WhereString != '')) 
        SET @Where = ' WHERE ' + @WhereString;
    IF((@OrderType IS NOT NULL) AND (@OrderType > 0))
    BEGIN
        SET @Order = ' ORDER BY ' + @OrderField;
        IF(@OrderType = 1)
            SET @Order = @Order + ' ASC';
        ELSE IF(@OrderType = 2)
            SET @Order = @Order + ' DESC';
        SET @Order2 = REPLACE(REPLACE(UPPER(@Order),' ASC',' {ASC}'),' DESC',' {DESC}');
        SET @Order2 = REPLACE(REPLACE(UPPER(@Order2),' {ASC}',' DESC'),' {DESC}',' ASC');
    END
     
    -- 重新统计总记录数
    IF(@RecorderCount <= 0)
        EXECUTE('SELECT COUNT(*) AS [RecorderCount] FROM ' + @TableName + @Where);
    ELSE
        SELECT @RecorderCount AS [RecorderCount];
     
    IF(@PageIndex <= 1) -- 如果是第一页
    BEGIN
        SET @MSSQL = 'SELECT TOP ' + STR(@PageSize) +' '+ @FieldNames + ' FROM ' + @TableName + @Where + @Order;
    END
    ELSE IF((@PageSize*@PageIndex) >= @RecorderCount) -- 如果是最后一页
    BEGIN
        SET @MSSQL = 'SELECT ' + @FieldNames + ' FROM (SELECT TOP ' + STR(@PageSize - ((@PageSize*@PageIndex) - @RecorderCount)) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order2 + ') AS [PagerTempTable]' + @Order;
    END
    ELSE
    BEGIN
        IF(@OrderType < 3) -- 单列排序分页方法
        BEGIN
            SET @MSSQL = 'SELECT TOP ' + STR(@PageSize);
            SET @MSSQL = @MSSQL + ' ' + @FieldNames;
            SET @MSSQL = @MSSQL + ' FROM ' + @TableName;
            SET @MSSQL = @MSSQL + @Where;
            DECLARE @TempStrings NVARCHAR(500);
            SET @TempStrings = '';
            IF(@OrderType > 0)
            BEGIN
                IF(@Where <> '')
                    SET @TempStrings = @TempStrings + ' AND';
                ELSE
                    SET @TempStrings = ' WHERE ';
                SET @TempStrings = @TempStrings + ' ' + @OrderField;
                 
                IF(@OrderType = 1)
                BEGIN
                    SET @TempStrings = @TempStrings + ' > (SELECT MAX';
                END
                ELSE
                BEGIN
                    SET @TempStrings = @TempStrings + ' < (SELECT MIN';
                END
                SET @TempStrings = @TempStrings + '(' + @OrderField + ') FROM (SELECT TOP '+STR(@LowerBound)+' ' + @OrderField + ' FROM ' + @TableName + @Where + @Order;
                SET @TempStrings = @TempStrings + ') AS [PagerTempTable])';
                 
            END
            SET @MSSQL = @MSSQL + @TempStrings + @Order;
        END
        ELSE -- 多列排序分页方法
        BEGIN
            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 + ';'
        END
    END
    EXECUTE(@MSSQL);
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!