发信人: dongbao()
整理人: dongbao(2000-10-27 23:19:04), 站内信件
|
作者：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_in
tPageSize 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(TotalChild
s)
from BBS as b
where a.RootID
= b.RootID) ,
'LastReplyTime' = (select max(PostTime
)
from BBS as
c
where a.Root
ID = 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 , PostTim
e
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 , P
ostTime) > 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(TotalChild
s)
from BBS as b
where a.RootID
= b.RootID) ,
'LastReplyTime' = (select max(PostTime
)
from BBS as
c
where a.Root
ID = 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 , P
ostTime) > getdate()
order by RootID desc , Layer , PostTim
e
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取下一条记录。
--
ICQ:43395237 OICQ:126132
我自豪我用正版，我骄傲我用盗版！！！
※ 来源:．月光程序代码网 http://www.moon-soft.com．[FROM: 202.108.0.79]
|
|