預存程序分頁

來源:互聯網
上載者:User
預存程序|分頁 作者: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取下一條記錄。



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。