日期:2014-05-18  浏览次数:20501 次

有关分页的存储过程
CREATE   procedure   p_splitpage      
@sql   nvarchar(4000),
@currentpage   int=2,
@pagesize   int=10,
@recordcount   int=0   output,
@pagecount   int=0   output
  as
set   nocount   on
declare   @p1   int
exec   sp_cursoropen   @p1   output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount   output
set   @recordcount=@pagecount
  --计算总页数      
IF       ISNULL(@pagesize,0) <1          
SET       @pagesize=10      
SET       @pagecount=(@pagecount+@pagesize-1)/@pagesize      
IF       ISNULL(@currentpage,0) <1   or   ISNULL(@currentpage,0)> @pagecount
SET       @currentpage=1            
ELSE      
SET       @currentpage=(@currentpage-1)*@PageSize+1
select   @recordcount   recordcount   ,@pagecount     pagecount,@currentpage     currentpage
exec   sp_cursorfetch   @p1,16,@currentpage,@pagesize  
exec   sp_cursorclose   @p1
set   nocount   off
GO

我想让传递的当前页面(@currentpage大于总页数)@pagecount的话,当前页面就是总页数,如何修改这段存储过程?谢谢,在线等


------解决方案--------------------
CREATE procedure p_splitpage
@sql nvarchar(4000),
@currentpage int=2,
@pagesize int=10,
@recordcount int=0 output,
@pagecount int=0 output
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordcount=@pagecount
--计算总页数
IF ISNULL(@pagesize,0) <1
SET @pagesize=10
SET @pagecount=(@pagecount+@pagesize-1)/@pagesize
IF ISNULL(@currentpage,0) <1 or ISNULL(@currentpage,0)> @pagecount
SET @currentpage=1
ELSE if @currentpage> @pagecount
set @currentpage=@pagecount
else
SET @currentpage=(@currentpage-1)*@PageSize+1
select @recordcount recordcount ,@pagecount pagecount,@currentpage currentpage
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
set nocount off
GO