通用分页存储过程（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里，那就是啥都可以用了。希望对朋友们有帮助