SQL存储过程实现分页

CREATE PROCEDURE Pagination

    @aTableName     NVARCHAR(255),              -- 需要查询的表名
    @aGetFields     NVARCHAR(1000)  = '*',      -- 需要返回的列,逗号隔开
    @anOrderField   NVARCHAR(255)   = '',       -- 排序的字段名(只能有一个)
    @aPageSize      INT             = 10,       -- 页尺寸
    @aPageIndex     INT             = 1,        -- 页码
    @anIsCount      BIT             = 0,        -- 是否仅仅返回记录总数, 0: 不返回, 非0: 返回
    @anIsDESC       BIT             = 0,        -- 是否升序排列, 0: 升序, 非0: 值则降序
    @aQuery         NVARCHAR(1500)  = ''        -- 查询条件 (注意: 不要加 where)

AS

DECLARE @strSQL     NVARCHAR(4000)              -- SQL主语句
DECLARE @strLocate  NVARCHAR(200)               -- 定位查询范围
DECLARE @strOrder   NVARCHAR(400)               -- 排序

IF @anIsCount != 0  -- 统计总数
BEGIN
    IF @aQuery != ''        -- 有查询条件
        SET @strSQL = 'select count(*) as Total from [' + @aTableName + '] where ' + @aQuery
    ELSE                    -- 没有查询条件
        SET @strSQL = 'select count(*) as Total from [' + @aTableName + ']'
END
ELSE                -- 不进行统计总数
BEGIN
    IF @anIsDESC != 0       -- 降续
    BEGIN
        SET @strLocate = ' < (select min'
        SET @strOrder = ' order by [' + @anOrderField + '] desc'
    END
    ELSE                    -- 升序
    BEGIN
        SET @strLocate = ' > (select max'
        SET @strOrder = ' order by [' + @anOrderField + '] asc'
    END
   
    IF @aPageIndex = 1      -- 第一页
    BEGIN
        IF @aQuery != ''        -- 有查询条件
            SET @strSQL = 'select top ' + STR(@aPageSize) + ' ' + @aGetFields + ' from [' + @aTableName + '] where ' + @aQuery + ' ' + @strOrder
        ELSE                    -- 没有查询条件
            SET @strSQL = 'select top ' + STR(@aPageSize) + ' ' + @aGetFields + ' from [' + @aTableName + '] '+ @strOrder
    END
    ELSE                    -- 不是第一页
    BEGIN
        IF @aQuery != ''        -- 有查询条件
            SET @strSQL = 'select top ' + STR(@aPageSize) + ' ' + @aGetFields                           -- select top 页尺寸 输出字段
            + ' from [' + @aTableName                                                                   -- from 表名
            + '] where [' + @anOrderField + ']' + @strLocate + '(['    + @anOrderField                  -- where 排序字段 >/< (select max/min(排序字段))
                + ']) from (select top ' + STR((@aPageIndex-1)*@aPageSize) + ' [' + @anOrderField       --      from (select top (页尺寸-1)*页码 排序字段
                    + '] from [' + @aTableName                                                          --          from 表名
                    + '] where ' + @aQuery + ' '                                                        --          where 查询条件
                    + @strOrder                                                                         --          排序
                + ') as tblTmp) and ' + @aQuery + ' '                                                   --      查询条件
            + @strOrder                                                                                 -- 排序
        ELSE
            SET @strSQL = 'select top ' + STR(@aPageSize) + '  '+ @aGetFields                           -- select top 页尺寸 输出字段
            + ' from [' + @aTableName                                                                   -- from 表名
            + '] where [' + @anOrderField + ']' + @strLocate + '(['+ @anOrderField                      -- where 排序字段 >/< (select max/min(排序字段))
                + ']) from (select top ' + STR((@aPageIndex-1)*@aPageSize) + ' ['+ @anOrderField        --      from (select top (页尺寸-1)*页码 排序字段
                    + '] from [' + @aTableName + ']'                                                    --          from 表名
                    + @strOrder                                                                         --          排序
                + ') as tblTmp)'                                                                        --
            + @strOrder                                                                                 -- 排序
    END                                                                                                
END  

EXEC (@strSQL)