存储过程分页

---（文/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取下一条记录。