欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

bbs的数据结构和存储过程(三)

程序员文章站 2022-03-10 15:00:38
/*************************************************************************//* *//* procedure : up_g...
/*************************************************************************/
/* */
/* procedure : up_getpostedtopiclist */
/* */
/* description: 精华区贴子列表 */
/* */
/* parameters: @a_intforumid : 版面id */
/* @a_intpageno: 页号 */
/* @a_intpagesize: 每页显示数,以根贴为准 */
/* */
/* use table: bbs , forum */
/* */
/* author: bigeagle@163.net */
/* */
/* 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: bigeagle@163.net */
/* */
/* 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