存储过程|数据|数据结构/*************************************************************************/
/* */
/* procedure : up_GetForumList */
/* */
/* Description: 取得版面列表 */
/* */
/* Parameters: None */
/* */
/* Use table: forum , bbsuser */
/* */
/* Author: bigeagle163.net */
/* */
/* Date: 2000/2/10 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetForumList'))
drop proc up_GetForumList
go

create proc up_GetForumList
as
select a.id , a.rootid , a.fatherid , a.layer , a.title , a.topiccount , a.description ,
'UserID'=b.id , b.UserName , b.Email , b.Homepage , b.Signature
from forum as a join BBSUser as b on a.Masterid=b.ID order by rootid , layer
go
select id , title , rootid from forum
up_getforumlist


/*************************************************************************/
/* */
/* procedure : up_InsertForum */
/* */
/* Description: 新建版面 */
/* */
/* Parameters: a_strName : 版面名称 */
/* a_strDescription: 版面描述 */
/* a_intFatherID: 分类ID，如果是0说明是大分类 */
/* */
/* Use table: forum */
/* */
/* Author: bigeagle163.net */
/* */
/* Date: 2000/4/23 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_InsertForum'))
drop proc up_InsertForum
go

create proc up_InsertForum a_strName varchar(50) , a_strDescription varchar(255) , a_intFatherID tinyint
as
/*定义局部变量*/
declare intLayer tinyint
declare intRootID tinyint

/*如果是版面并且没有指定分类，则返回-1*/
if(a_intFatherID <> 0 and not exists(select * from forum where id = a_intFatherID))
return(-1)

/*根据a_intFatherID计算layer , rootid*/
if(a_intFatherID = 0)
begin
select intLayer = 0
select intRootID = 0
end
else
begin
select intLayer = 1
select intRootID = a_intFatherID
end

Insert into Forum(rootid , layer , fatherid , title , description)
values(intRootID , intLayer , a_intFatherID , a_strName , a_strDescription)
if (a_intFatherID = 0)
begin
select intRootID = identity
update Forum set rootid = intRootID where id = intRootID
end
go

/*************************************************************************/
/* */
/* procedure : up_DeleteForum */
/* */
/* Description: 删除版面 */
/* */
/* Parameters: a_intForumID : 版面id */
/* */
/* Use table: forum */
/* */
/* Author: bigeagle163.net */
/* */
/* Date: 2000/4/23 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_DeleteForum'))
drop proc up_DeleteForum
go

create proc up_DeleteForum a_intForumID tinyint
as
delete from Forum where id = a_intForumID
delete from Forum where RootID = a_intForumID
go

select id , title , rootid , fatherid from forum

/*************************************************************************/
/* */
/* procedure : up_PostTopic */
/* */
/* Description: 发贴子 */
/* */
/* Parameters: a_intForumID : 版面id */
/* a_intFatherID: 父贴ID，如果是新主题为0 */
/* a_strSubject: 标题 */
/* a_strContent: 内容 */
/* a_intUserID: 发贴人ID */
/* a_intFaceID: 表情ID */
/* a_strIP: 发贴人IP */
/* */
/* Use table: bbs , forum , bbsuser */
/* */
/* Author: bigeagle163.net */
/* */
/* Date: 2000/2/13 */
/* */
/* History: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_PostTopic'))
drop proc up_PostTopic
go

create proc up_PostTopic
a_intForumID int ,
a_intFatherID int ,
a_strSubject varchar(255) ,
a_strContent text ,
a_intUserID int ,
a_intFaceID int ,
a_strIP varchar(255)
as
/*定义局部变量*/
declare intRootID int --根id
declare dblOrderNum float(53) --排序基数
declare intLayer int --层
declare dblNextOrderNum float(53) --下一回贴的ordernum

/*判断有没有这个版面*/
if not exists(select * from forum where id = a_in