熱點推薦:
您现在的位置: 電腦知識網 >> 操作系統 >> Windows系統管理 >> 正文

存儲過程分頁

2013-11-11 21:43:23  來源: Windows系統管理 

  
  
  
  
  
   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 ) +
  
   if @a_intDays = 如果不限定天數
   begin
   /*求符合條件記錄數*/
   select RecordCount = count(*)
   from BBS where Layer= and ForumID = @a_ForumID
  
   /*輸出紀錄*/
   /*首先定義可滾動光標*/
   set rowcount @m_intRecordNumber
   declare m_curTemp Scroll cursor
   for
   select aID aTitle dUserName aFaceID
   ContentSize = datalength(aContent)
   TotalChilds = (select sum(TotalChilds)
   from BBS as b
   where aRootID = bRootID)
   LastReplyTime = (select max(PostTime)
   from BBS as c
   where aRootID = cRootID)
   from BBS as a
   join BBSUser as d on aUserID = dID
   where Layer= and ForumID = @a_ForumID
   order by RootID desc Layer PostTime
   open m_curTemp
   fetch absolute @m_intStartRecord from m_curTemp
   while @@fetch_status =
   fetch next from m_curTemp
  
   set rowcount
   /*清場*/
   CLOSE m_curTemp
   DEALLOCATE m_curTemp
   end
  
   else 如果限定天數
  
   begin
   /*求符合條件記錄數*/
   select RecordCount = count(*)
   from BBS where Layer= and ForumID = @a_ForumID
   and dateadd(day @a_intDays PostTime) > getdate()
  
   /*輸出紀錄*/
   /*首先定義可滾動光標*/
   set rowcount @m_intRecordNumber
   declare m_curTemp Scroll cursor
   for
   select aID aTitle dUserName aFaceID
   ContentSize = datalength(aContent)
   TotalChilds = (select sum(TotalChilds)
   from BBS as b
   where aRootID = bRootID)
   LastReplyTime = (select max(PostTime)
   from BBS as c
   where aRootID = cRootID)
   from BBS as a
   join BBSUser as d on aUserID = dID
   where Layer= 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 =
   fetch next from m_curTemp
  
   set rowcount
   /*清場*/
   CLOSE m_curTemp
   DEALLOCATE m_curTemp
   end
   go
  
  
   注若在asp中調用存儲過程的command對象為cm則set rs=cmexecute然後用set rs=rsnextrecordset取下一條記錄

From:http://tw.wingwit.com/Article/os/xtgl/201311/9080.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.