关于分页办法

March 25,2004

时间关系，没有经过完全测试，这个是大概意思。
CREATE PROCEDURE getnotes

@forumID varchar(10),
@a_intPageNo int ,
@a_intPageSize int,
@rootID varchar(10)
AS
   declare @m_intRecordNumber int
   declare @m_intStartRecord  int
   declare @pagecount int
   declare @temp int
   declare @end  int
   declare @sql varchar(500)
   declare @bbsname varchar(25)
   declare @articles int
   declare @manager varchar(30)
   declare @selectrootID varchar(500)
   declare @temprootID varchar(10)

   select @m_intRecordNumber = @a_intPageSize * @a_intPageNo
   select @m_intStartRecord = @a_intPageSize * (@a_intPageNo - 1) + 1

   set nocount on
   select @bbsname=subjectname,@manager=manager,@articles=articles from bbs_subjects where subjectID=@forumID
   if @rootID='0'
      begin
      -- select @sql='declare m_curTemp Scroll cursor for select ID,title,shrink,rootID,orderID,pubtime,hits,bytes,username,email from bbs_forum_'+@forumID+'  WHERE rootID in (select distinct rootID from bbs_forum_'+@forumID+' ) order by rootID desc,orderID asc'
         select @sql='declare m_curTemp Scroll cursor for select rootID from bbs_forum_'+@forumID+' where rootID=parentID order by rootID desc'
      end
   else
      select @sql='declare m_curTemp Scroll cursor for select ID,title,shrink,rootID,orderID,pubtime,hits,bytes,username,email from bbs_forum_'+@forumID+' where rootID='+@rootID+' order by orderID asc'

   exec(@sql)
   open m_curTemp

   set @pagecount = case
         when  @@cursor_rows % @a_intPageSize=0 then @@cursor_rows / @a_intPageSize
         when  @@cursor_rows % @a_intPageSize<>0 then @@cursor_rows / @a_intPageSize+1
   end
   if  @@cursor_rows<@a_intPageSize and @@cursor_rows>0
      begin
         select @pagecount=1
      end


if @rootID='0'
   begin
      set @temp = 1
      set @selectrootID='0'
      fetch absolute @m_intStartRecord from m_curTemp into @temprootID
      while  @@fetch_status = 0 and @temp < @a_intPageSize
         begin
               set @temp = @temp + 1
               select @selectrootID=@selectrootID+','+@temprootID
               fetch next from m_curTemp into @temprootID
         end
      CLOSE m_curTemp
      DEALLOCATE m_curTemp
      set nocount off
      select 'pagecount' = @pagecount
      select 'bbsname'=@bbsname
      select 'manager'=@manager
      select 'articles'=@articles
      select @sql='declare curTemp Scroll cursor for select ID,title,shrink,rootID,orderID,Images,pubtime,hits,bytes,username,email  from  bbs_forum_'+@forumID+' where rootID in ('+@selectrootID+') order by rootID desc,orderID asc'
      exec(@sql)
      open curTemp
      fetch first from curTemp
      while  @@fetch_status = 0
         begin
               fetch next from curTemp
         end
      CLOSE curTemp
      DEALLOCATE curTemp
   end
else
      begin
         set @temp = 1
         set nocount off
         fetch absolute @m_intStartRecord from m_curTemp
         while  @@fetch_status = 0 and @temp < @a_intPageSize
         begin
               set @temp = @temp + 1
               fetch next from m_curTemp
         end
         CLOSE m_curTemp
         DEALLOCATE m_curTemp
      end