存储过程分页

–创建

create  procedure FenYe

@sqlstr nvarchar(4000), –查询字符串

@currentpage int, –第N页

@pagesize int –每页行数

as

set nocount on

declare @P1 int, –P1是游标的id

 @rowcount int

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

select ceiling(1.0*@rowcount/@pagesize) as 总页数–,@rowcount as 总行数,@currentpage as 当前页

set @currentpage=(@currentpage-1)*@pagesize+1

exec sp_cursorfetch @P1,16,@currentpage,@pagesize

exec sp_cursorclose @P1

set nocount off

–测试它

declare   @pagecount   int,–总页数  

@recordcount   int –记录数  

exec FenYe ‘select * from business.dbo.tb_cocacola_webflux’,1,20,@pagecount out,@recordcount  out

select   @pagecount   as   pagecount,@recordcount   as   recordcount       

declare @p1 int,

@rowcount int

exec   sp_cursoropen   @p1   output,’select * from business.dbo.tb_cocacola_webflux’,@scrollopt=1,@ccopt=1,@rowcount=@rowcount   output  

select @p1,@rowcount

exec sp_cursorfetch @p1,16,1,20

exec sp_cursorclose @p1

这是个很经典的存储过程分页,如果用在JAVA中则不用创建它,直接用con.prepareCall(“{?=call sp_cursoropen(?, ?, ?, ?, ?)}”)和con.prepareCall(“{?=call sp_cursorfetch(?, ?, ?, ?)}”)就可以了,网上看到很多人在问,这里做下说明