Sql Server 分页存储过程

CREATE PROCEDURE [dbo].[up_GetListByPage]
    (
        @TableName VarChar(255),        -- 表名
        @Fields VarChar(1000) = '*',    -- 需要返回的列
        @OrderField VarChar(255) = '',  -- 排序的字段名
        @IsAsc Bit = 0,                 -- 是否升序
        @StrWhere VarChar(1500) = '',   -- 查询条件 (注意: 不要加 WHERE)
        @PageSize Int = 10,             -- 每页数量
        @PageIndex Int = 1,             -- 当前页
        @IsDoCount Bit = 0              -- 是否查询记录总数
    )
AS

    SET NOCOUNT ON
    
        DECLARE
            @strCountSQL NVARCHAR(2000),
            @strSQL NVARCHAR(4000),
            @strTmp VARCHAR(110),
            @strOrder VARCHAR(400)
                    
        IF(@IsDoCount != 0)
            BEGIN
                --ListCount
                IF @strWhere !=''
                    SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @TableName + '] WHERE '+@strWhere
                ELSE
                    SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @TableName + ']'
            END
            --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
        ELSE
            BEGIN
                --List
                IF @IsAsc = 0
                    BEGIN
                        SET @strTmp = '<(SELECT MIN'
                        SET @strOrder = ' ORDER BY [' + @OrderField +'] DESC'
                        --如果@IsAsc是0，就执行降序，这句很重要
                    END
                ELSE
                    BEGIN
                        SET @strTmp = '>(SELECT MAX'
                        SET @strOrder = ' ORDER BY [' + @OrderField +'] ASC'
                    END

                IF @PageIndex = 1
                    BEGIN
                        IF @StrWhere != ''
                            SET @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM [' + @TableName + '] WHERE ' + @StrWhere + ' ' + @strOrder
                        ELSE
                            SET @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM ['+ @TableName + '] '+ @strOrder
                        --如果是第一页就执行以上代码，这样会加快执行速度
                    END
                ELSE
                    BEGIN
                        --以下代码赋予了@strSQL以真正执行的SQL代码
                        SET @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM ['
                        + @TableName + '] WHERE [' + @OrderField + ']' + @strTmp + '(['+ @OrderField + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['+ @OrderField + '] FROM [' + @TableName + ']' + @strOrder + ') AS tblTmp)'+ @strOrder
                        IF @StrWhere != ''
                        SET @strSQL = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM ['
                        + @TableName + '] WHERE [' + @OrderField + ']' + @strTmp + '(['
                        + @OrderField + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['
                        + @OrderField + '] FROM [' + @TableName + '] WHERE ' + @StrWhere + ' '
                        + @strOrder + ') AS tblTmp) AND ' + @StrWhere + ' ' + @strOrder
                    END
            END
            
            EXEC (@strSQL)
    SET NOCOUNT OFF