/*************************************************************************/ /* */ /* PRocedure : up_GetPostedTopicList */ /* */ /* Description: 精華區貼子列表 */ /* */ /* Parameters: @a_intForumID : 版面id */ /* @a_intPageNo: 頁號 */ /* @a_intPageSize: 每頁顯示數，以根貼為準 */ /* */ /* 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 /*定義局部變量*/ declare @intBeginID int declare @intEndID int declare @intRootRecordCount int declare @intPageCount int declare @intRowCount int /*關閉計數*/ set nocount on
/*檢測是否有這個版面*/ if not exists(select * from forum where id = @a_intForumID) return (-1)
/*求總共根貼數*/ select @intRootRecordCount = count(*) from bbs where posted=1 and forumid=@a_intForumID if (@intRootRecordCount = 0) --如果沒有貼子，則返回零 return 0
/*求開始rootID*/ set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1 /*限制條數*/ set rowcount @intRowCount select @intBeginID = rootid from bbs where posted=1 and forumid=@a_intForumID order by id desc
/*結束rootID*/ set @intRowCount = @a_intPageNo * @a_intPageSize /*限制條數*/ set rowcount @intRowCount select @intEndID = rootid from bbs where posted=1 and forumid=@a_intForumID order by id desc
/*恢復系統變量*/ 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: 取貼子 */ /* */ /* Parameters: @a_intTopicID : 貼子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 not exists (select * from bbs where id = @a_intTopicID) return (-1)
/*更新該貼的點擊數*/ 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
if exists(select * from sysobjects where id = object_id('up_DeleTopic')) drop proc up_DeleTopic go
create proc up_DeleTopic @a_intTopicID int as
/*定義局部變量*/ declare @intRootID int declare @intLayer int declare @floatOrderNum float(53) declare @floatNextOrderNum float(53) declare @intCounts int declare @intForumID int
/*取消計數*/ set nocount on
/*首先查找這個貼子的rootid和ordernum，沒有則返回*/ select @intRootID = RootID , @floatOrderNum = OrderNum , @intLayer = layer , @intForumID = forumid from bbs where id = @a_intTopicID if @@rowcount = 0 return (-1)
/*取下一個同層貼子的ordernum*/ select @FloatNextOrderNum = isnull(max(ordernum) , 0) from bbs where RootID=@intRootID and layer=@intLayer and ordernum < @floatOrderNum
/*多表操作，用事務*/ begin transaction
/*首先刪貼*/
delete from bbs where rootid=@intRootID and ordernum > @floatNextOrderNum and ordernum <= @floatOrderNum select @intCounts = @@rowcount if (@@error != 0) goto Error
/*論壇貼子數減少*/ update forum set topiccount = topiccount - @intCounts where id=@intForumID if (@@error != 0) goto Error
/*完成事務，返回*/ commit transaction set nocount off return(0)
Error: rollback transaction set nocount off return (-1) go
select forumid from bbs update bbs set forumid=4
/*************************************************************************/ /* */ /* procedure : up_GetUserInfo */ /* */ /* Description: 取得發貼人信息 */ /* */ /* Parameters: @a_strUserName : 用戶筆名 */ /* */ /* 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_GetUserInfo go
create proc up_GetUserInfo @a_strUserName varchar(20) as declare @m_intOrder int --排名 declare @m_intPoint int --積分
set nocount on
/*如果沒有找到該用戶，則返回-1*/ select @m_intPoint = point from bbsuser where username=@a_strUserName if(@@rowcount = 0) return(-1)
/*求排名*/ select @m_intOrder = count(*) + 1 from bbsuser where point > @m_intPoint
select * , 'order' = @m_intOrder from bbsuser where username=@a_strUserName
if exists(select * from sysobjects where id= object_id('up_postedtopic')) drop proc up_postedtopic go
create proc up_PostedTopic @a_intTopicID int as /*定義局部變量*/ declare @m_intUserID int --發貼人ID
/*查找是否有這個貼子*/ select @m_intUserID = userid from bbs where id = @a_intTopicID if(@@rowcount != 1) return -1
/*因為對兩個表操作所以用事務*/ begin transaction update bbs set posted = 1 where id = @a_intTopicID if(@@error <> 0) goto Error update bbsuser set point = point + 3 where id = @m_intUserID if(@@error <> 0) goto Error Commit transaction return (0) Error: rollback transaction go
