存储过程分页

March 25,2004

作者：bigeagle

if exists(select * from sysobjects where ID = object_id("up_TopicList"))
drop proc up_TopicList
go

create proc up_TopicList
         @a_ForumID int , @a_intDays int , @a_intPageNo int , @a_intPageSize tinyint
as
   declare @m_intRecordNumber int
   declare @m_intStartRecord  int
   select @m_intRecordNumber = @a_intPageSize * @a_intPageNo
   select @m_intStartRecord = @a_intPageSize * (@a_intPageNo - 1) + 1

   if @a_intDays = 0                   --如果不限定天数
      begin
            /*求符合条件记录数*/
            select "RecordCount" = count(*)
                     from BBS where Layer=1 and ForumID = @a_ForumID

            /*输出纪录*/
            /*首先定义可滚动光标*/
            set rowcount @m_intRecordNumber
            declare m_curTemp Scroll cursor
                     for
                        select a.ID ,a.Title , d.UserName , a.FaceID ,
                              'ContentSize' = datalength(a.Content) ,
                              'TotalChilds' = (select sum(TotalChilds)
                                                      from BBS as b
                                                      where a.RootID = b.RootID) ,
                              'LastReplyTime' = (select max(PostTime)
                                                      from BBS as c
                                                      where a.RootID = c.RootID)
                              from BBS as a
                                 join BBSUser as d on a.UserID = d.ID
                              where Layer=1 and ForumID = @a_ForumID
                              order by RootID desc , Layer , PostTime
            open m_curTemp
            fetch absolute @m_intStartRecord from m_curTemp
            while  @@fetch_status = 0
                  fetch next from m_curTemp

            set rowcount 0
            /*清场*/
            CLOSE m_curTemp
            DEALLOCATE m_curTemp
      end

   else                               --如果限定天数

      begin
            /*求符合条件记录数*/
            select "RecordCount" = count(*)
                     from BBS where Layer=1 and ForumID = @a_ForumID
                                    and dateadd(day , @a_intDays , PostTime) > getdate()

            /*输出纪录*/
            /*首先定义可滚动光标*/
            set rowcount @m_intRecordNumber
            declare m_curTemp Scroll cursor
                     for
                        select a.ID ,a.Title , d.UserName , a.FaceID ,
                              'ContentSize' = datalength(a.Content) ,
                              'TotalChilds' = (select sum(TotalChilds)
                                                      from BBS as b
                                                      where a.RootID = b.RootID) ,
                              'LastReplyTime' = (select max(PostTime)
                                                      from BBS as c
                                                      where a.RootID = c.RootID)
                              from BBS as a
                                 join BBSUser as d on a.UserID = d.ID
                              where Layer=1 and ForumID = @a_ForumID
                                    and dateadd(day , @a_intDays , PostTime) > getdate()
                              order by RootID desc , Layer , PostTime
            open m_curTemp
            fetch absolute @m_intStartRecord from m_curTemp
            while  @@fetch_status = 0
                  fetch next from m_curTemp

            set rowcount 0
            /*清场*/
            CLOSE m_curTemp
            DEALLOCATE m_curTemp
      end
go

注：若在asp中调用存储过程的command对象为cm，则set rs=cm.execute，然后用set rs=rs.nextrecordset取下一条记录。