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 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= 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 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= 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=cm execute 然后用set rs=rs nextrecordset取下一条记录
