Data structure and stored procedure of BBS (III.)

Stored Procedures | data | structure/*************************************************************************/
/* */
* Procedure:up_getpostedtopiclist * *
/* */
/* Description: The essence area posts list *
/* */
/* Parameters: @a_intForumID: Layout ID * *
/* @a_intPageNo: Page number * *
/* @a_intPageSize: Per-page display number, with the root paste as quasi * *
/* */
* * 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 int
DECLARE @intEndID int
DECLARE @intRootRecordCount int
DECLARE @intPageCount int
DECLARE @intRowCount int
/* Close Count * *
SET NOCOUNT ON

/* Detect if there is this page * *
If not EXISTS (SELECT * FROM forum where id = @a_intForumID)
Return (-1)

/* Ask for a total number of root paste * *
Select @intRootRecordCount = count (*) from BBS where posted=1 and forumid= @a_intForumID
if (@intRootRecordCount = 0)--returns zero if no posts
return 0

* To determine whether the number of pages is correct *
if (@a_intPageNo-1) * @a_intPageSize > @intRootRecordCount
Return (-1)

* * Ask to start rootid*/
Set @intRowCount = (@a_intPageNo-1) * @a_intPageSize + 1
/* Limit number of bars * *
SET ROWCOUNT @intRowCount
Select @intBeginID = Rootid from BBS where posted=1 and forumid= @a_intForumID
ORDER BY id DESC

/* End rootid*/
Set @intRowCount = @a_intPageNo * @a_intPageSize
/* Limit number of bars * *
SET ROWCOUNT @intRowCount
Select @intEndID = Rootid from BBS where posted=1 and forumid= @a_intForumID
ORDER BY id DESC

/* RESTORE SYSTEM Variable * *
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: Take the post * *
/* */
/* Parameters: @a_intTopicID: Posts 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 there is no this post * *
If not EXISTS (SELECT * FROM BBS where id = @a_intTopicID)
Return (-1)

/* Update the number of clicks on this post * *
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
From BBS as a join Bbsuser as B on a.userid = b.ID
where a.id = @a_intTopicID
Go

Up_gettopic 11

/*************************************************************************/
/* */
* Procedure:up_deletopic * *
/* */
/* Description: Delete posts and child stickers, update the sender information * *
/* */
/* Parameters: @a_intTopicID: Posts 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_deletopic
Go

create proc up_deletopic @a_intTopicID int
As

/* Define Local Variables * *
DECLARE @intRootID int
DECLARE @intLayer int
Declare @floatOrderNum float (53)
Declare @floatNextOrderNum float (53)
DECLARE @intCounts int
DECLARE @intForumID int

/* Cancel Count * *
SET NOCOUNT ON

/* First find this post Rootid and Ordernum, no then return * * *
Select @intRootID = Rootid,
@floatOrderNum = Ordernum,
@intLayer = layer,
@intForumID = Forum