存储过程分页

--创建
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(?, ?, ?, ?)}")就可以了,网上看到很多人在问,这里做下说明