/ ************************************************** *********************** /
/ * * /
/ * Procedure: Up_getforumlist * /
/ * * /
/ * DESCRIPTION: Take 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_geetforumlist'))
Drop Proc Up_GetforumList
Go
Create Proc Up_GetforumListasselect A.ID, A.Rootid, A.fatherId, A.Layer, A.TITLE, A.TOPICCOUNT, A.DESCRIPTION, 'UserId' = B.ID, B. HOSERNAME, B.EMAIL, B.Homepage , B. SignatureFrom Forum forum as a join bbsuser as b on a.masterid = B.ID ORDER by rootid, layergoselect ID, title, rootid from forumup_getforumlist
/ ************************************************** ************************ / / * * // * procedure: up_insertforum * // * * // * Description: New layout * // * * // * parameters: @a_strname: Layout Name * // * @a_strdescription: Layout Description * // * @a_intfatherid: Category ID, if it is 0 description is a big 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_Insertforumogo
create proc up_InsertForum @a_strName varchar (50), @a_strDescription varchar (255), @a_intFatherID tinyintas / * define local variables * / declare @intLayer tinyintdeclare @intRootID tinyint / * If the layout is not specified and classified -1 * / IF (@A_INTFATHERID <> 0 and not exists (SELECT * FROM where id = @a_intfatherid)) RETURN (-1) / * calculates layer, rootid * / if (@a_intfatherid = 0) beginselect @intlayer = 0select @A_INTFATHERID = 0) @Introotid = 0ndelsebeginselect @intlayer = 1select @Introotid = @a_intfatheridend
Insert into Forum (rootid, layer, fatherid, title, description) values (@intRootID, @intLayer, @a_intFatherID, @a_strName, @a_strDescription) if (@a_intFatherID = 0) beginselect @intRootID = @@ identityupdate Forum set rootid = @ Introotid WHERE ID = @introotidendgo
/ ************************************************** ************************ / / * * // * 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_Deleteforumogo
Create proc up_deleteforum @a_intforumid tinyintasdelete from forum where id = @a_intforumiddelete from forum where rootid = @a_intforumidgo
Select ID, Title, Rootid, FatherId from Forum
/ ************************************************** *********************** / / * * // * procedure: up_posttopic * // * * // * Description: Poston * // * * // * parameters: @a_intforumid: Layout ID * // * @a_intfatherid: Pupid ID, if it is a new topic 0 * // * @a_strsubject: Title * // * @a_strContent: content * // * @ A_InTuserid: Posted by the person ID * // * @a_intfaceid: Emure ID * // * @a_strip: Posted by ip * /////////wwwwle: 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_PostTopicgocreate 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 iddeclare @dblOrderNum float (53) - radix sort declare @intLayer int - Layerne Declare @dblnextordernum float (53) - Next back ORDERNUM
/ * Judging whether there is this version * / if not exists (SELECT * from forum where id = @a_intforumid) Return (-1)
/ * Judgment new post or respond to post * / if (@a_intfatherid = 0) - Root Begin select @introotid = isnull (max (id), 0) 1 from bbsselect @dblordernum = 9e 24select @intlayer = 1ENDELSE - Post back beginselect @introotid = rootid, @intlayer = layer 1, @dblordernum = OrdernumFrom bbs where id = @a_intfatherid
/ * If you have not found a father's post, return error * / if (@@ rowcount = 0) Return -1 / * calculates ORDERNUM * / SELECT @dblnextordernum = ISNULL (Max (ORDERNUM), 0) from bbs where ordernum <@dblordernum and ROOTID = @ \Trootidselect @dblordernum = (@dblordernum @dblnextordernum) / 2 end / * Due to two tables, transaction * / begin transaction / * Insert Post * / Insert Into BBS (Rootid, FatherId, Layer, Ordernum , UserID, forumID, Subject, Content, FaceID, IP) values (@intRootID, @a_intFatherID, @intLayer, @dblOrderNum, @a_intUserID, @a_intForumID, @ a_strSubject, @a_strContent, @a_intFaceID, @a_strIP) / * determine success * / if (@@ Error! = 0) goto onerror
/ * Update layout number * / update forum set topiccount = Topiccount 1 where id = @a_intforumidif (@@ error! = 0) goto onerror / * Update User Score * / Update Bbsuser set point = Point 1 where id = @a_intuseridif (@@ Error! = 0) goto Onerror / * Execution * / Commit TransactionReturn (0)
/ * Error handling * / OneRror: rollback transactionreturn (-1)
Go Select ID from bbs where fatherid = 0 Order by rootid desc, Ordernum desc up_posttopic 1, 12, 'Hahaha, laugh,' Hello, World ', 1, 1,' 203.93.95.10 '
/ ************************************************** ************************ / / * * // * procedure: Up_gettopiclist * // * * // * Description: Post list * // * * // * parameters: @a_intforumid: Layout ID * // * @a_intpageno: Page number * // * @a_intpagesize: The number per page is displayed in the root stickers * // * // * Use Table: BBS , Forum * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/2/14 * // * * // * History: * // * * // * *********************************************************** ********************** / if EXISTS (SELECT * from sysobjects where id = Object_id ('up_gettopiclist') Drop Proc Up_gettopiclistgocreate proc up_gettopiclist @a_intforumid int, @ a_intPageNo int, @ a_intPageSize int as / * define local variables * / declare @intBeginID intdeclare @intEndID intdeclare @intRootRecordCount intdeclare @intPageCount intdeclare @intRowCount int / * Close count * / set nocount on / * detecting whether the forum * / if not exists (select * from forum where id = @a_intForumID) return (-1) / * find the number of total root paste * / select @intRootRecordCount = count (*) from bbs where fatherid = 0 and forumid = @ a_intForumIDif (@intRootRecordCount = 0 ) - Returns if there is no post Zero Return 0 / * Correct page number * / if (@a_intpageno - 1) * @a_intpagesize> @IntrootrecordcountReturn (-1)
/ * Start request rootID * / set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize 1 / * limit the number of * / set rowcount @intRowCountselect @intBeginID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID order by ID DESC
/ * End rootID * / set @intRowCount = @a_intPageNo * @ a_intPageSize / * limit the number of * / set rowcount @intRowCountselect @intEndID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID order by id desc
/ * Restore system variable * / set rowcount 0set nocount off
