通用分页存储过程

USE Hotel
go
--带偏移量的适合首页数据的存储过程
CREATE PROC PaginationWithOffsetInFirstPage
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @offset INT ,--偏移量(页中如果有别的数据需要插入，就赋此参数)
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS 
    BEGIN 
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where 
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize - @offset) + @columns
            + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + '  where ' + @where
            + ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END 
go 
--以下是调用示例
USE Hotel
go
CREATE PROC PaginationWithOffsetNotInFirstPage
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @offset INT ,--偏移量(页中如果有别的数据需要插入，就赋此参数,默认为零)
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS 
    BEGIN 
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where 
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize) + @columns + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + ' where ' + @where
            + ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize
                                             - @offset)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END 
go  
USE Hotel 
go 

--调用首页数据的存储过程,page必须传1
DECLARE @d DATETIME
SET @d = GETDATE()

DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000) 
EXECUTE dbo.PaginationWithOffsetInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
    'dbo.TB_HotelList', -- varchar(100)
    'hotelId', -- varchar(100)
    'asc', -- varchar(50)
    '1=1 and cityId=215', -- varchar(100)
    1, -- int
    20, -- int
    5,
    @pageCount OUTPUT, -- int
    @totalCount OUTPUT
SELECT  STR(@pageCount) ,
        @totalCount
SELECT  '语句执行花费时间(毫秒)' = DATEDIFF(ms, @d, GETDATE())
    go
 
 
 
--调用非首页数据的存储过程
DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000) 
EXECUTE dbo.PaginationWithOffsetNotInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
    'dbo.TB_HotelList', -- varchar(100)
    'hotelId', -- varchar(100)
    'asc', -- varchar(50)
    '1=1 and cityId=215', -- varchar(100)
    3, -- int
    20, -- int
    5,
    @pageCount OUTPUT, -- int
    @totalCount OUTPUT
SELECT  STR(@pageCount) ,
        @totalCount   
        
        
--不带偏移量的通用 存储过程
CREATE PROC Pagination
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS 
    BEGIN 
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where 
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize) + @columns
            + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + ' with(nolock) where ' + @where
            + ') as A  where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END 
go