/ ************************************************** *********************** /
/ * * /
/ * Procedure: Up_getpostedtopiclist * /
/ * * /
/ * DESCRIPTION: Essence area Post List * /
/ * * /
/ * Parameters: @a_intforumid: Layout ID * /
/ * @a_intpageno: Page number * /
/ * @A_INTPAGESIZE: The number per page is displayed as the root sticker * /
/ * * /
/ * 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 / * define local variables * / declare @intBeginID intdeclare @intEndID intdeclare @intRootRecordCount intdeclare @intPageCount intdeclare @intRowCount int / * Close Count * / set nocount on / * detected Is there this layout * / if not exists (Select * from forum where id = @a_intforumid) Return (-1) / * seeks a total of total roots * / select @Introotrecordcount = count (*) from bbs where posted = 1 and forumID @ a_intforumidif (@introotrecordcount = 0) - If there is no post, return zero return 0 / * judgment page number correct * / 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 posted = 1 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 posted = 1 and forumid = @ a_intForumID order by id desc / * recovery system variables * / set rowcount 0set 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.PointFrom b. A Join bbsuser as b on a.userid = B.Idwhere posted = 1 and forumid = @ a_intforumid and a.rootid betWeen @ intEndID and @intBeginIDorder by a.rootid desc, a.ordernum desc return (@@ rowcount) - select @@ rowcountgo select id, rootid, fatherid, forumid, posted from bbsup_getpostedtopiclist 3, 1, 20 / ****** *********************************************************** ***************** / / * * // * procedure: Up_gettopic * // * * // * Description: Abstracts 3 /// * * // * parameters: @A_INTTTOPICID: Post 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_gettopicgo
Create Proc Up_gettopic @a_inttopiciD Intas / * If this post * / if not exists (SELECT * from bbs where id = @a_inttopicid) Return (-1) / * Update this post Hits * / 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.SignatureFrom BBS as a join bbsuser as b on a.userid = B.ID WHERE A.ID = @a_inttopicidgo
UP_GETTOPIC 11
/ ************************************************** ************************ / / * * // * procedure: up_deletopic * // * * // * Description: Delete Posts and Pick Update Posting Information * // * * // * Parameters: @a_inttopicid: Post ID * / / * * // * Use Table: BBS * / / / * * // * Author: BiGeagle@163.net * // * * // * Date: 2000/2/24 * // * * // * history: * // * * // ******************* *********************************************************** **** /
If EXISTS (SELECT * from sysobjects where id = Object_id ('up_deletopic')) Drop Proc Up_Deletopicgo
Create proc up_deletopic @a_inttopicid Int as
/ * Define local variables * / declare @intRootID intdeclare @intLayer intdeclare @floatOrderNum float (53) declare @floatNextOrderNum float (53) declare @intCounts intdeclare @intForumID int / * Cancel count * / set nocount on
/ * First, look up the post of rootid and ordernum, no return * / select @intRootID = RootID, @floatOrderNum = OrderNum, @ intLayer = layer, @ intForumID = forumidfrom bbs where id = @a_intTopicID if @@ rowcount = 0 return (-1)
/ * Remove one of the same-level posts * / select @floatnextordernum = isnull (max (ordernum), 0) from bbswhere rootid = @ INTROOTID AND = @ Intlayer and ORdernum <@floatorNum
/ * Multi-table operation, with a transaction * / begin transaction / * First Shantie * / delete from bbs where rootid = @ intRootID and ordernum> @floatNextOrderNumand ordernum <= @floatOrderNum select @intCounts = @@ rowcountif (@@ error! = 0) Goto Error
/ * Forum Post Number * / Update Forum Set Topiccount = Topiccount - @intcounts where id = @ Intforumidif (@@ Error! = 0) goto error
/ * Complete the transaction, return * / commit transactions set nocount offreturn (0)
Error: Rollback TransactionsEt NoCount Offreturn (-1) Go
SELECT Forum ID = 4 / ********************************************************************************************************************************************************************************************* ********************************* / / * * / / * procedure: Up_getuserinfo * // * * // * Description: Number of posting people * // * * // * parameters: @a_strusername: User pen name * // * * // * Use table: bbsuser * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/4/16 * // * * // * history: * // * * // ***************** *********************************************************** ***** / if EXISTS (SELECT * from sysobjects where id = Object_id ('up_getuserinfo')) Drop Proc Up_getuserInfogo
Create proc up_getuserinfo @a_strusername varchar (20) asdeclare @m_intorder int - Rank Declare @m_intpoint int - Points
Set nocount on
/ * If the user is not found, return -1 * / select @m_intpoint = point from bbsuser where username = @ a_strusernameif (@@ rowcount = 0) Return (-1)
/ * Ask for ranking * / select @m_intorder = count (*) 1 from bbsuser where point> @m_intpoint
SELECT *, 'Order' = @m_intorder from bbsuser where username = @ a_strusername
Set NoCount Offgoup_getuserinfo 'Liao Jiayuan'
/ ************************************************** ************************ / / * * / / * procedure: up_postedtopic * // * * // * Description: Transfer post into the essence District * // * * // * parameters: @a_inttopicid Post ID * // * * // * Use Table: BBS, Postedtopic * // * * // * Author: BiGeagle@163.net * // * * // * Date: 2000/4/17 * // * * // * history: * // * * // ******************************* *********************************************************** /
If EXISTS (SELECT * from sysobjects where id = Object_id ('up_postedtopic')) Drop Proc Up_Postedtopicgo
CREATE PROC UPOPICTOPIC @A_INTTOPICID INTAS / * Defines Local Variables * / Declare @m_intuserid Int - Posted by ID
/ * Find no this post * / select @m_intuserid = userid from bbs where id = @a_inttopicidif (@@ rowcount! = 1) Return -1 / * Because of two tables, use transactions * / begin TransactionUpdate BBS set posted = 1 where id = @a_intTopicIDif (@@ error <> 0) goto Errorupdate bbsuser set point = point 3 where id = @m_intUserIDif (@@ error <> 0) goto ErrorCommit transactionreturn (0) error: rollback transactiongo
