sql通用存储过程分页

一、使用说明

       构建好查询sql后，传入相应参数，调用存储过程，即可得到分页数据

       提示：如有指定排序字段，在查询sql结尾添加上就好，无需单独传参

二、存储过程脚本

       

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    潇潇与偕
-- Create date: 2022-03-08
-- Description:    分页存储过程
-- =============================================
create PROCEDURE p_Common_DataPager_New
@sqlstr nvarchar(max), --sql查询脚本
@currentpage int,    --当前页码
@pagesize int,      --每页显示行数
@rowcount int output, --输出参数：总行数
@pagecount int output  --输出参数：总页数

AS
BEGIN
    set nocount on 
    declare @countSql nvarchar(max)
    declare @dataSql nvarchar(max)
    declare @dbwhitespaceIndex int=0
    declare @orderbyStr nvarchar(max)
    
    
    
    --处理掉多余空格
    set @dbwhitespaceIndex=CHARINDEX('  ',@sqlStr)
    while @dbwhitespaceIndex>0
    begin
     set @sqlStr=REPLACE(@sqlStr,'  ',' ')
     set @dbwhitespaceIndex=CHARINDEX('  ',@sqlStr)
    end
--组装无排序字段的查询sql
    if CHARINDEX('ORDER BY',@sqlStr)=0
        begin
            --截取排序字段
            set @orderbyStr=''
            set @dataSql='declare @tempTable table 
                (
                  TemplTableSortId int
                );
                select top '+CONVERT(nvarchar(10),@pagesize)+' dt.* from (
                select ROW_NUMBER() over(order by t.TemplTableSortId) sortid,s.* from ('+@sqlstr+') s
                left join @tempTable t on 0=1) dt
                where dt.sortid> '+convert(nvarchar(10),(@currentpage-1)*@pagesize)
        end
    --组装有排序字段的sql
    else
    begin
         select @orderbyStr= SUBSTRING(@sqlStr,CHARINDEX('ORDER BY',@sqlStr),LEN(@sqlStr))
         set @dataSql='select top '+CONVERT(nvarchar(10),@pagesize)+' dt.* from 
                             ( select ROW_NUMBER() over('+@orderbyStr+') sortid,'+ SUBSTRING(@sqlStr,CHARINDEX('SELECT',@sqlStr)+6,LEN(@sqlStr)-len(@orderbyStr)-6)+') dt where dt.sortid>'+convert(nvarchar(10),(@currentpage-1)*@pagesize)
    end

    --查询总行数
    set @countSql='select @totalcount=count(*)  from ('+ SUBSTRING(@sqlStr,0,LEN(@sqlStr)-len(@orderbyStr))+') t'

    exec sp_executesql @countSql, N'@totalcount int out',@rowcount out

    --计算总页数
    set @pagecount=@rowcount/@pagesize

    if @rowcount%@pagesize>0
    begin
      set @pagecount+=1
    end
    
    exec(@dataSql)
    
    set nocount off 
END
GO