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
