SQLServer存储过程实现高效分页查询

CREATE PROCEDURE [dbo].[Pagination_SingleCondition] (
    @TableName NVARCHAR(255),
    @SelectColumns NVARCHAR(1000) = '*',
    @WhereCondition NVARCHAR(1000) = '',
    @OrderByColumn NVARCHAR(255) = '',
    @OrderType INT = 0,
    @PageSize INT = 10,
    @PageIndex INT = 1,
    @ShowPageCount BIT = 0,
    @ShowRecordCount BIT = 0,
    @TotalRecords INT OUTPUT,
    @TotalPages INT OUTPUT
) AS
BEGIN
    DECLARE @MainQuery NVARCHAR(MAX);
    DECLARE @TempVar NVARCHAR(100);
    DECLARE @OrderByClause NVARCHAR(400);

    SET @TotalRecords = 0;
    SET @TotalPages = 0;

    IF @ShowRecordCount = 1 OR @ShowPageCount = 1
    BEGIN
        IF LEN(@WhereCondition) > 0
            SET @MainQuery = 'SELECT @TotalRecords = COUNT(*) FROM [' + @TableName + '] WHERE ' + @WhereCondition;
        ELSE
            SET @MainQuery = 'SELECT @TotalRecords = COUNT(*) FROM [' + @TableName + ']';

        EXEC sp_executesql @MainQuery, N'@TotalRecords INT OUT', @TotalRecords OUT;
    END

    IF @ShowPageCount = 1
    BEGIN
        IF @TotalRecords <= @PageSize
            SET @TotalPages = 1;
        ELSE
        BEGIN
            SET @TotalPages = @TotalRecords / @PageSize;
            IF (@TotalRecords % @PageSize) > 0
                SET @TotalPages = @TotalPages + 1;
        END
    END

    IF @OrderType = 1
    BEGIN
        SET @TempVar = '(SELECT MIN(';
        SET @OrderByClause = ' ORDER BY [' + @OrderByColumn + '] DESC';
    END
    ELSE
    BEGIN
        SET @TempVar = '(SELECT MAX(';
        SET @OrderByClause = ' ORDER BY [' + @OrderByColumn + '] ASC';
    END

    IF @PageIndex = 1
    BEGIN
        IF LEN(@WhereCondition) > 0
            SET @MainQuery = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @SelectColumns + ' FROM [' + @TableName + '] WHERE ' + @WhereCondition + ' ' + @OrderByClause;
        ELSE
            SET @MainQuery = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @SelectColumns + ' FROM [' + @TableName + '] ' + @OrderByClause;
    END
    ELSE
    BEGIN
        IF LEN(@WhereCondition) > 0
            SET @MainQuery = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @SelectColumns + ' FROM [' + @TableName + '] WHERE [' + @OrderByColumn + ']' + @TempVar + '[' + @OrderByColumn + ']) FROM (SELECT TOP ' + CAST((@PageIndex - 1) * @PageSize AS NVARCHAR(10)) + ' [' + @OrderByColumn + '] FROM [' + @TableName + '] WHERE ' + @WhereCondition + ' ' + @OrderByClause + ') AS TempTable) AND ' + @WhereCondition + ' ' + @OrderByClause;
        ELSE
            SET @MainQuery = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @SelectColumns + ' FROM [' + @TableName + '] WHERE [' + @OrderByColumn + ']' + @TempVar + '[' + @OrderByColumn + ']) FROM (SELECT TOP ' + CAST((@PageIndex - 1) * @PageSize AS NVARCHAR(10)) + ' [' + @OrderByColumn + '] FROM [' + @TableName + '] ' + @OrderByClause + ') AS TempTable) ' + @OrderByClause;
    END

    EXEC (@MainQuery);
END