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

/*************************************************************************/
/* */
/* 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