Data structure and stored procedure of BBS (II.)

Stored Procedures | data | structure/*************************************************************************/
/* */
* Procedure:up_getforumlist * *
/* */
/* Description: Get the layout list * *
/* */
* Parameters:none * *
/* */
* * Use Table:forum, Bbsuser * *
/* */
* Author:bigeagle@163.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 in a.masterid=b.id order by Rootid, layer
Go
Select ID, title, Rootid from forum
Up_getforumlist


/*************************************************************************/
/* */
* Procedure:up_insertforum * *
/* */
/* Description: New layout * *
/* */
/* Parameters: @a_strName: page Name * *
/* @a_strDescription: Layout Description * *
/* @a_intFatherID: Category ID, if 0 description is a large classification * *
/* */
* * Use Table:forum * *
/* */
* Author:bigeagle@163.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 (m), @a_strDescription varchar (255), @a_intFatherID tinyint
As
/* Define Local Variables * *
DECLARE @intLayer tinyint
DECLARE @intRootID tinyint

/* If it is a layout and does not specify a category, then return -1*/
if (@a_intFatherID <> 0 and NOT EXISTS (SELECT * FROM forum where id = @a_intFatherID))
Return (-1)

/* Calculates layer according to @a_intfatherid, 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: Delete layout * *
/* */
/* Parameters: @a_intForumID: Layout ID * *
/* */
* * Use Table:forum * *
/* */
* Author:bigeagle@163.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: Post Zi * *
/* */
/* Parameters: @a_intForumID: Layout ID * *
/* @a_intFatherID: Parent Sticker ID, if the new theme is 0 * *
/* @a_strSubject: Title * *
/* @a_strContent: Content * *
/* @a_intUserID: Sender ID * *
/* @a_intFaceID: Expression ID * *
/* @a_strIP: Sender IP * *
/* */
/* Use Table:bbs, forum, Bbsuser * *
/* */
* Author:bigeagle@163.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
/* Define Local Variables * *
DECLARE @intRootID INT--Root ID
Declare @dblOrderNum float (53)--Sort cardinality
DECLARE @intLayer INT--Layer
Declare @dblNextOrderNum float (53)--Ordernum of the next reply

/* Judge If there is no this page * *
If not EXISTS (SELECT * FROM forum where id = @a_in