【干货】SqlServer 总结几种存储过程分页的使用

就我而言写代码最烦的就是处理数据，其中之一就是分页的使用。

有的代码写多了，总结出一套适用自己的分页方法；有的查一下资料借鉴一下套用起来也达到目的。

那么小编在这里给大家总结几个方法供大家做一下参考。

分页方法一：

set ansi_nulls on
go
set quoted_identifier on
go/*********************************************************  
* 作    用：数据分页
* 作    者：ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 创建日期：2019-05-13
* 使用说明：
    --调用例子：
    --1.单表/单排序
    exec proc_datapagination @tablenames='bigtable',@primarykey='d_id',@fields='d_id,d_title,d_content,d_time',@pagesize=20,@currentpage=1,@filter ='',@group='',@order='d_id desc'
    --2.单表/多排序
    exec proc_datapagination 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
    --3.多表/单排序
    exec proc_datapagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.bigtable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
    --4.多表/多排序
    exec proc_datapagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.bigtable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'

*********************************************************/  
 create procedure [dbo].[proc_datapagination]  
@tablenames varchar(200),    --表名，可以是多个表，但不能用别名
@primarykey varchar(100),    --主键，可以为空，但@order为空时该值不能为空
@fields    varchar(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@pagesize int,            --每页记录数
@currentpage int,        --当前页，0表示第1页
@filter varchar(200) = '',    --条件，可以为空，不用填 where
@group varchar(200) = '',    --分组依据，可以为空，不用填 group by
@order varchar(200) = ''    --排序，可以为空，为空默认按主键升序排列，不用填 order by
as
begin
    declare @sortcolumn varchar(200)
    declare @operator char(2)
    declare @sorttable varchar(200)
    declare @sortname varchar(200)
    if @fields = ''
        set @fields = '*'
    if @filter = ''
        set @filter = 'where 1=1'
    else
        set @filter = 'where ' +  @filter
    if @group <>''
        set @group = 'group by ' + @group

    if @order <> ''
    begin
        declare @pos1 int, @pos2 int
        set @order = replace(replace(@order, ' asc', ' asc'), ' desc', ' desc')
        if charindex(' desc', @order) > 0
            if charindex(' asc', @order) > 0
            begin
                if charindex(' desc', @order) < charindex(' asc', @order)
                    set @operator = '<='
                else
                    set @operator = '>='
            end
            else
                set @operator = '<='
        else
            set @operator = '>='
        set @sortcolumn = replace(replace(replace(@order, ' asc', ''), ' desc', ''), ' ', '')
        set @pos1 = charindex(',', @sortcolumn)
        if @pos1 > 0
            set @sortcolumn = substring(@sortcolumn, 1, @pos1-1)
        set @pos2 = charindex('.', @sortcolumn)
        if @pos2 > 0
        begin
            set @sorttable = substring(@sortcolumn, 1, @pos2-1)
            if @pos1 > 0 
                set @sortname = substring(@sortcolumn, @pos2+1, @pos1-@pos2-1)
            else
                set @sortname = substring(@sortcolumn, @pos2+1, len(@sortcolumn)-@pos2)
        end
        else
        begin
            set @sorttable = @tablenames
            set @sortname = @sortcolumn
        end
    end
    else
    begin
        set @sortcolumn = @primarykey
        set @sorttable = @tablenames
        set @sortname = @sortcolumn
        set @order = @sortcolumn
        set @operator = '>='
    end

    declare @type varchar(50)
    declare @prec int
    select @type=t.name, @prec=c.prec
    from sysobjects o 
    join syscolumns c on o.id=c.id
    join systypes t on c.xusertype=t.xusertype
    where o.name = @sorttable and c.name = @sortname
    if charindex('char', @type) > 0
    set @type = @type + '(' + cast(@prec as varchar) + ')'

    declare @toprows int
    set @toprows = @pagesize * @currentpage + 1
    print @toprows
    print @operator
    exec('
        declare @sortcolumnbegin ' + @type + '
        set rowcount ' + @toprows + '
        select @sortcolumnbegin=' + @sortcolumn + ' from  ' + @tablenames + ' ' + @filter + ' ' + @group + ' order by ' + @order + '
        set rowcount ' + @pagesize + '
        select ' + @fields + ' from  ' + @tablenames + ' ' + @filter  + ' and ' + @sortcolumn + '' + @operator + '@sortcolumnbegin ' + @group + ' order by ' + @order + '    
    ')    
end

go

 

方法二：

/*********************************************************  
* 作    用：数据分页
* 作    者：ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 创建日期：2019-05-13
* 使用说明：

　　　　　　declare @pagecount int 
　　　　　　exec commonpagination 　'job_id,job_desc','jobs','job_id', 'asc','1=1',2,2,@pagecount output 
　　　　　　--select '总页数为：' + str(@pagecount)

*********************************************************/

create  procedure commonpagination
@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  --总页数，输出参数 
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 传参 
    if( @recordcount % @pagesize = 0) --如果总记录条数可以被页大小整除 
        set @pagecount = @recordcount / @pagesize --总页数就等于总记录条数除以页大小 
    else --如果总记录条数不能被页大小整除 
        set @pagecount = @recordcount / @pagesize + 1 --总页数就等于总记录条数除以页大小加1 
    
    set @sqlselect = 
    n'select '+@columns+' from ( 
    select row_number() over (order by ' 
    +@ordercolumnname+' '+@order 
    +') as tempid,* from ' 
    +@tablename+' where '+ @where 
    +') as temptablename where tempid between ' 
    +str((@pageindex - 1)*@pagesize + 1 ) 
    +' and '+str( @pageindex * @pagesize) 
    exec (@sqlselect) --执行动态sql 
end 

 

方法三：

create procedure commonpagination
(@tablename   varchar(2000),         --表名
 @refieldsstr varchar(1000) = '*',     --字段名(全部字段为*)
 @orderstring varchar(200),         --排序字段(必须!支持多字段不用加order by)
 @wherestring varchar(500) = n'',     --条件语句(不用加where)
 @pagesize    int,                     --每页多少条记录
 @pageindex   int          = 1,     --指定当前为第几页
 @totalrecord int output            --返回总记录数
)
as
     begin    
    --处理开始点和结束点
         declare @startrecord int;
         declare @endrecord int;
         declare @totalcountsql nvarchar(500);
         declare @sqlstring nvarchar(2000);
         set @startrecord = (@pageindex - 1) * @pagesize + 1;
         set @endrecord = @startrecord + @pagesize - 1;
         set @totalcountsql = n'select @totalrecord = count(*) from '+@tablename;--总记录数语句
         set @sqlstring = n'(select row_number() over (order by '+@orderstring+') as rowid,'+@refieldsstr+' from '+@tablename;--查询语句
    -- 判断条件是否为空
         if(@wherestring != ''
            or @wherestring != null)
             begin
                 set @totalcountsql = @totalcountsql+'  where '+@wherestring;
                 set @sqlstring = @sqlstring+'  where '+@wherestring;
         end;
    --返回总记录数
         exec sp_executesql
              @totalcountsql,
              n'@totalrecord int out',
              @totalrecord output;
    ----执行主语句
         set @sqlstring = 'select * from '+@sqlstring+') as t where rowid between '+ltrim(str(@startrecord))+' and '+ltrim(str(@endrecord));
         exec (@sqlstring);
     end;

 

方式四：

/*********************************************************  
* 作    用：数据分页
* 作    者：ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 创建日期：2019-05-13
* 使用说明：

　　　　　　[usp_getpagedata] 'select * from 表名',1,10

*********************************************************/

create procedure [dbo].[usp_getpagedata]
(                         
    @sqlstr varchar(8000)    -- 查询的sql语句 
    , @curpage int          -- 当前页面位置
    , @pagesize int            -- 页面显示的数据行数
)
as
begin
    set nocount on
    declare     @p1 int   --游标
                , @rowcount int
                , @countpage int
                , @currow int

    exec sp_cursoropen @p1 output, @sqlstr, @scrollopt = 1, @ccopt = 1, @rowcount = @rowcount output

    if @rowcount % @pagesize > 0
        set @countpage = @rowcount / @pagesize + 1
    else
        set @countpage = @rowcount / @pagesize

    if @curpage > @countpage
        set @curpage = @countpage

    set @currow = (@curpage - 1) * @pagesize + 1

    set nocount off
    select @curpage curpaeg, @pagesize pagesize, @countpage countpage, @rowcount [rowcount]
    exec sp_cursorfetch @p1, 16, @currow, @pagesize
    set nocount on
    exec sp_cursorclose @p1
end

 

还有更多分页方式，感兴趣的可以去查查资料，上面的分页方式已经足够参考了，

然后结合自身情况可以写一个量身定做的分页方法，后续直接使用就可以了。