分页存储过程

ALTER procedure [dbo].[SP_splitpage]
(
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=1, --要显示的页码
@pagesize int=50 --每页的大小
)
as
begin
set nocount on
declare @p1 int,@pagecount int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @pagecount=ceiling(1.0*@pagecount/@pagesize)
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
select @pagecount as pageCount
end
GO

exec SP_splitpage '查询语句',当前面页,每页大小