拍拍尘土

cREATE PROCEDURE [dbo].[PAGERDATA_P]
@sqlstr nvarchar(3500), --SQL字串
@curpage int,--第N頁
@pagesize int--每頁行數
AS
begin
--select @pagecount=3,@pagesize=3 ,@sqlstr='select * from news order by distribute_date desc, ID desc'

set nocount on
declare @P1 int,--P1是游标的id
@rowcount int, @pagecount int
SET @sqlstr = RTRIM(@sqlstr)
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pagecount = ceiling(1.0*@rowcount/@pagesize)
if @curpage<=1
set @curpage =1
if @curpage>@pagecount
set @curpage=@pagecount
select @rowcount as Total, @pagecount as PageCount,@curpage as CurPage
set @curpage=(@curpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@curpage,@pagesize
exec sp_cursorclose @P1
end
GO


--**************************************************
--存储过程RhSplitPageData,返回相应的分页表的统计数据
--**************************************************


CREATE PROCEDURE [dbo].[RhSplitPageData]

@sqlstr nvarchar(3500), --SQL语句
@curpage int, --第n页
@pagesize int --每页行数
--@rowcount int out, --行数
--@pagecount int out --页数
AS
begin
set nocount on

  --设置参数
declare @P1 int ,@rowcount int, @pagecount int

SET @sqlstr = RTRIM(@sqlstr)
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount out
set @pagecount = ceiling(1.0*@rowcount/@pagesize)
if @curpage<=1
set @curpage =1
if @curpage>@pagecount
set @curpage=@pagecount
select @rowcount as Total, @pagecount as PageCount,@curpage as CurPage --返回统计数据
set @curpage=(@curpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@curpage,@pagesize --返回查询页的数据
exec sp_cursorclose @P1
end
GO