通用分页存储过程（MSSQL）

刚刚工作遇到的第一个问题就是大量数据的查询和插入，通过查找大概了解了一下数据库的查询计划，最后决定用存储过程来实现优化查询的问题。贴一个通用的分页存储过程：

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 

注释是带偏移量的首页数据，举个例子，假如我要查两张表，第一张表正常查就行，第二张表因为是数据量比较大，所以要用分页来读。假设第一张表查到7条记录，第二张表查的时候给的页索引和页大小分别是1，100，就是查第一页但最重要100条数据，那么就要把第二张表查出来的最后七条数据抹掉，这个偏移量在这里就是7.但是这个计算方法只适合于查询第二张表的首页记录。下面贴第二张表非首页的存储过程。

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  

还蛮好用的，比直接用sql拼分页语句快了一些。主要这个方便啊，以后放到自己的codebase里，那就是啥都可以用了。希望对朋友们有帮助