/*************************************************************************/ /* */ /* 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
