用存储过程实现分页

文件名:            GetRecordsPagination.sql
功能:                创建存储过程GetRecordsPagination     注:只能用于能用MAX,MIN等方法的字段进行分页!

1.没有使用ROW_NUMBER()的存储过程

 IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE SPECIFIC_NAME = 'GetRecordsPagination')
DROP PROCEDURE GetRecordsPagination

GO

CREATE PROCEDURE GetRecordsPagination
(
    @tableName            varchar(256),       -- 进行分页表名
    @columnName            varchar(256),        -- 用作分页的字段名
    @pageSize            int = 10,           -- 每页记录数 (默认是10)
    @pageIndex            int = 1,            -- 页码 (默认第一页)
    @sortType            bit = 0,            -- 数据查找排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
    @visibleColumns        varchar(2000) = '',    -- 要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
    @sqlCondition        varchar(2000) = '', -- 附加查询条件 (不包含'where'字串)     
    @orderByColumn        varchar(256) = '',    -- 记录最后排序OrderBy的字段 (默认等于@columnName)
    @orderBySortType    bit = 0                -- 记录最后排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
)
AS

declare @strSQL            varchar(8000)        -- 最终合成的SQL语句
declare @strTemp        varchar(256)        -- 临时字符串
declare @strOrderBy        varchar(1000)       -- 查找排序类型子句

-- 设置数据查找排序类型子句
if @sortType != 0
begin
    set @strTemp = '<(select min'
    set @strOrderBy = ' order by [' + @columnName + '] desc'
end
else
begin
    set @strTemp = '>(select max'
    set @strOrderBy = ' order by [' + @columnName +'] asc'
end

-- 设置要返回字段
if @visibleColumns = ''
    set  @visibleColumns = ' * '

-- 设置查找语句
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
    + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
    + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
    + @columnName + '] from [' + @tableName + ']' + @strOrderBy + ') as TempTable)'
    + @strOrderBy

-- 添加附加查询条件
if @sqlCondition != ''
    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
        + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
        + @columnName + '] from [' + @tableName + '] where ' + @sqlCondition + ' '
        + @strOrderBy + ') as TempTable) and ' + @sqlCondition + ' ' + @strOrderBy

-- 优化第一页查询
if @pageIndex = 1
begin
    set @strTemp = ''
    if @sqlCondition != ''
        set @strTemp = ' where (' + @sqlCondition + ')'

    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + ']' + @strTemp + ' ' + @strOrderBy
end

-- 设置自定义输出OrderBy
if @orderByColumn != ''
begin
    set @strTemp = @strSQL
    if @orderBySortType = 0 
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] asc' 
    else
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] desc'
end  

--测试生成的SQL语句
--PRINT @strSQL

exec (@strSQL)

 

 

 2.有使用ROW_NUMBER()的存储过程

/**/ /***********************************************************************
* 文件名:            GetRecordsPaginationWithRowNumber.sql
* 功能:                创建存储过程GetRecordsPaginationWithRowNumber
                    [注:运用SQL 2005新增函数ROW_NUMBER(),有局限性!]
***********************************************************************/

IF   EXISTS  (
     SELECT   *
     FROM  INFORMATION_SCHEMA.ROUTINES 
     WHERE  SPECIFIC_NAME  =   ' GetRecordsPaginationWithRowNumber ' )
DROP   PROCEDURE  GetRecordsPaginationWithRowNumber

GO

CREATE   PROCEDURE  GetRecordsPaginationWithRowNumber
(
     @tableName              varchar ( 256 ),        --  进行分页表名
     @columnName              varchar ( 256 ),         --  用作分页的字段名
     @pageSize              int   =   10 ,            --  每页记录数 (默认是10)
     @pageIndex              int   =   1 ,             --  页码 (默认第一页)
     @sortType              bit   =   0 ,             --  数据查找排序类型, 
                                             --  0 升序,1 降序 (默认 0)
     @visibleColumns          varchar ( 2000 )  =   '' ,     --  要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
     @sqlCondition          varchar ( 2000 )  =   '' ,  --  附加查询条件 (不包含'where'字串)     
     @orderByColumn          varchar ( 256 )  =   '' ,     --  记录最后排序OrderBy的字段 (默认等于@columnName)
     @orderBySortType      bit   =   0                  --  记录最后排序类型, 
                                             --  0 升序,1 降序 (默认 0)
)
AS

declare   @strSQL              varchar ( 8000 )         --  最终合成的SQL语句
declare   @strTemp          varchar ( 256 )         --  临时字符串
declare   @strOrderBy          varchar ( 1000 )        --  查找排序类型子句

--  设置数据查找排序类型子句
if   @sortType   !=   0
begin
     set   @strOrderBy   =   '  order by [ '   +   @columnName   +   ' ] desc '
end
else
begin
     set   @strOrderBy   =   '  order by [ '   +   @columnName   + ' ] asc '
end

--  设置要返回字段
if   @visibleColumns   =   ''
     set    @visibleColumns   =   '  *  '

--  设置查找语句
set   @strSQL   =   ' select top  '   +   str ( @pageSize )  +   '   '   +   @visibleColumns   
                 +   '  from ( select * , ROW_NUMBER() Over (  '  
                 +   @strOrderBy   +   '  ) as RowNum from [ '   +   @tableName   +   ' ] ) as TempTable  '
                 +   '  where RowNum between  '   +   str ( ( @pageIndex - 1 )  *   @pageSize  ) 
                 +   '  and  '   +   str (  @pageIndex   *   @pageSize  )

--  添加附加查询条件
if   @sqlCondition   !=   ''
     set   @strSQL   =   ' select top  '   +   str ( @pageSize )  +   '   '   +   @visibleColumns  
                 +   '  from ( select * , ROW_NUMBER() Over (  '  
                 +   @strOrderBy   +   '  ) as RowNum from [ '   +   @tableName   +   ' ] where  '
                 +   @sqlCondition   +   '  ) as TempTable  '
                 +   '  where RowNum between  '   +   str ( ( @pageIndex - 1 )  *   @pageSize  ) 
                 +   '  and  '   +   str (  @pageIndex   *   @pageSize  )

--  优化第一页查询
if   @pageIndex   =   1
begin
     set   @strTemp   =   ''
     if   @sqlCondition   !=   ''
         set   @strTemp   =   '  where ( '   +   @sqlCondition   +   ' ) '

     set   @strSQL   =   ' select top  '   +   str ( @pageSize )  +   '   '   +   @visibleColumns   +   '   from [ '
         +   @tableName   +   ' ] '   +   @strTemp   +   '   '   +   @strOrderBy
end

--  设置自定义输出OrderBy
if   @orderByColumn   !=   ''
begin
     set   @strTemp   =   @strSQL
     if   @orderBySortType   =   0  
         set   @strSQL   =    '  select * from (  '   +   @strTemp   +   '  ) as TempTable2 order by [ '
             +   @orderByColumn   +   ' ] asc '  
     else
         set   @strSQL   =    '  select * from (  '   +   @strTemp   +   '  ) as TempTable2 order by [ '
             +   @orderByColumn   +   ' ] desc '
end   

-- 测试生成的SQL语句
PRINT   @strSQL

exec  ( @strSQL )