存储过程分页

王朝other·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航