当前页面位置: » 丰搜网 » 文档中心 » 详细内容
存储过程分页
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取下一条记录。
