bbs的数据结构和存储过程（三）

/*************************************************************************/
/* */
/* procedure : up_getpostedtopiclist */
/* */
/* description: 精华区贴子列表 */
/* */
/* parameters: @a_intforumid : 版面id */
/* @a_intpageno: 页号 */
/* @a_intpagesize: 每页显示数，以根贴为准 */
/* */
/* use table: bbs , forum */
/* */
/* author: [e-mail] */
/* */
/* date: 2000/2/14 */
/* */
/* history: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id(up_getpostedtopiclist))
drop proc up_getpostedtopiclist
go

create proc up_getpostedtopiclist
@a_intforumid int ,
@a_intpageno int ,
@a_intpagesize int
as
/*定义局部变量*/
declare @intbeginid int
declare @intendid int
declare @introotrecordcount int
declare @intpagecount int
declare @introwcount int
/*关闭计数*/
set nocount on

/*检测是否有这个版面*/
if not exists(select * from forum where id = @a_intforumid)
return (-1)

/*求总共根贴数*/
select @introotrecordcount = count(*) from bbs where posted=1 and forumid=@a_intforumid
if (@introotrecordcount = 0) --如果没有贴子，则返回零
return 0

/*判断页数是否正确*/
if (@a_intpageno - 1) * @a_intpagesize > @introotrecordcount
return (-1)

/*求开始rootid*/
set @introwcount = (@a_intpageno - 1) * @a_intpagesize + 1
/*限制条数*/
set rowcount @introwcount
select @intbeginid = rootid from bbs where posted=1 and forumid=@a_intforumid
order by id desc

/*结束rootid*/
set @introwcount = @a_intpageno * @a_intpagesize
/*限制条数*/
set rowcount @introwcount
select @intendid = rootid from bbs where posted=1 and forumid=@a_intforumid
order by id desc

/*恢复变量*/
set rowcount 0
set nocount off

select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.userid , a.fatherid , a.rootid ,
bytes = datalength(a.content) , b.username , b.email , b.homepage , b.signature , b.point
from bbs as a join bbsuser as b on a.userid = b.id
where posted=1 and forumid=@a_intforumid and a.rootid between @intendid and @intbeginid
order by a.rootid desc , a.ordernum desc
return(@@rowcount)
--select @@rowcount
go
select id , rootid , fatherid , forumid , posted from bbs
up_getpostedtopiclist 3 ,1 , 20
/*************************************************************************/
/* */
/* procedure : up_gettopic */
/* */
/* description: 取贴子 */
/* */
/* parameters: @a_inttopicid : 贴子id */
/* */
/* use table: bbs */
/* */
/* author: [e-mail] */
/* */
/* date: 2000/2/16 */
/* */
/* history: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id(up_gettopic))
drop proc up_gettopic
go

create proc up_gettopic @a_inttopicid int
as
/*如果没有这贴子*/
if not exists (select * from bbs where id = @a_inttopicid)
return (-1)

/*更新该贴的点击数*/
update bbs set hits = hits + 1 where id = @a_inttopicid

select a.* , bytes = datalength(a.content) ,
b.username , b.email , b.homepage , b.point , b.signature