游标分页的存储过程

create procedure Pages
(
@TableNames nvarchar(200), --表名
@PrimaryKey nvarchar(100), --主键(一定要有)
@Fields nvarchar(800)='*', --字段
@PageSize int, --每页记录数
@CurrentPage int, --当前页数
@Filter nvarchar(1000), --条件(不需要where)
@Order nvarchar(100) --排序(不需要order by,需要asc和desc字符)
)
as
declare @recordCount int,@pageCount int,@sql nvarchar(4000),@where varchar(800)--要执行的sql语句
set @pageCount=0
set @recordCount=0
if(len(@Filter)<1)
begin
set @where=''
end
else
begin
set @where=' where '+@Filter
end
if(len(@Order)>0)
begin
set @Order=''
end
else
begin
set @Order=' order by '+@Order
end
set @sql = 'select '+@Fields+' from '+@TableNames+ @where+@Order

set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,1,1,@pagecount output
--set @recordCount = @pageCount
--select @pagecount=ceiling(1.0*@pagecount/@PageSize)
-- ,@CurrentPage=(@CurrentPage-1)*@PageSize+1
print @p1
exec sp_cursorfetch @p1,16,@CurrentPage,@PageSize
exec sp_cursorclose @p1

GO

CREATE procedure p_splitpage
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out,--总记录数
@SearchTime int=0 out --计算耗时
as
declare @usetime datetime
set @usetime=getdate()
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
set @SearchTime=datediff(ms,@usetime,getdate())
print @SearchTime

GO