SQLsever存储过程分页查询

使用存储过程实现分页查询,sql语句如下:

use [datebasename]  --数据库名
go
/****** object:  storedprocedure [dbo].[pagination]    script date: 03/30/2019 10:36:52 ******/
set ansi_nulls on
go
set quoted_identifier on
go

create procedure [dbo].[pagination]
(
    @sqltable varchar(1000),--要查询的表或视图,也可以一句sql语句
    @sqlpk varchar(50),--主键
    @sqlfield varchar(1000),--查询的字段
    @sqlwhere varchar(1000)='', --查询条件 
    @sqlorder varchar(200),--排序
    @pagesize int=20,--每页的记录数
    @pageindex int=1, --第几页,默认第一页
    @iscount bit, --是否获取记录数
    @recordcount int=0 output
)
as
set nocount on
declare @pagelowerbound int
declare @pageupperbound int
declare @sqlstr nvarchar(2000)

--获取记录数
if @iscount=1
begin
    set @sqlstr=n'select @scount=count(1) from '+@sqltable+' where 1=1 '+@sqlwhere
    exec sp_executesql @sqlstr,n'@scount int output',@recordcount output
end

set @pagelowerbound=(@pageindex-1)*@pagesize
set @pageupperbound=@pagelowerbound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid varchar(100))
set rowcount @pageupperbound 
set @sqlstr=n'insert into #pageindex(nid) select '+@sqlpk+' from '+@sqltable+' where 1=1 '+@sqlwhere+' '+@sqlorder

exec sp_executesql @sqlstr
set @sqlstr='select '+@sqlfield+' from '+ @sqltable +' inner join #pageindex p on '+@sqlpk+'=p.nid and (p.id>'+str(@pagelowerbound)+') and (p.id<='+str(@pageupperbound)+')' +' '+@sqlorder

exec sp_executesql @sqlstr
set nocount off
drop table #pageindex

但是如果你有一些奇怪的需求,比如删除当前页数据之后不重新返回第一页,然后继续请求下一页,这时会出现有一下数据被跳过查询

解决方案如下:

use [datebasename]
go

/****** object:  storedprocedure [dbo].[pagination]    script date: 03/30/2019 14:41:39 ******/
set ansi_nulls on
go

set quoted_identifier on
go


create procedure [dbo].[paginationskip]
(
    @sqltable varchar(1000),--要查询的表或视图,也可以一句sql语句
    @sqlpk varchar(50),--主键
    @sqlfield varchar(1000),--查询的字段
    @sqlwhere varchar(1000)='', --查询条件 
    @sqlorder varchar(200),--排序
    @pagesize int=20,--每页的记录数
    @pageindex int=1, --第几页,默认第一页
    @iscount bit, --是否获取记录数
    @recordcount int=0 output,
    @skip int=0 --跳过记录数
)
as
set nocount on
declare @pagelowerbound int
declare @pageupperbound int
declare @sqlstr nvarchar(2000)

--获取记录数
if @iscount=1
begin
    set @sqlstr=n'select @scount=count(1) from '+@sqltable+' where 1=1 '+@sqlwhere
    exec sp_executesql @sqlstr,n'@scount int output',@recordcount output
end

set @pagelowerbound=(@pageindex-1)*@pagesize-@skip  --减去删除的条数,以适应需求
set @pageupperbound=@pagelowerbound+@pagesize-@skip 
create table #pageindex(id int identity(1,1) not null,nid varchar(100))
set rowcount @pageupperbound 
set @sqlstr=n'insert into #pageindex(nid) select '+@sqlpk+' from '+@sqltable+' where 1=1 '+@sqlwhere+' '+@sqlorder

exec sp_executesql @sqlstr
set @sqlstr='select '+@sqlfield+' from '+ @sqltable +' inner join #pageindex p on '+@sqlpk+'=p.nid and (p.id>'+str(@pagelowerbound)+') and (p.id<='+str(@pageupperbound)+')' +' '+@sqlorder

exec sp_executesql @sqlstr
set nocount off
drop table #pageindex
go

添加了一个 skip 参数,来指示需要往前推进几条数据,这个参数就是你在请求之前删除的条数