IDC资讯中心
/*************************************************************************/
/* */
/* 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
/*判断页数是否正确*/
if (@a_intpageno – 1) * @a_intpagesize > @introotrecordcount
return (-1)
/*求开始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
up_gettopic 11
/*************************************************************************/
/* */
/* procedure : up_deletopic */
/* */
/* description: 删除贴子及子贴，更新发贴人信息 */
/* */
/* parameters: @a_inttopicid : 贴子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
/*定义局部变量*/
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
set nocount off
go
up_getuserinfo 廖家远
/*************************************************************************/
/* */
/* procedure : up_postedtopic */
/* */
/* description: 将贴子转入精华区 */
/* */
/* parameters: @a_inttopicid 贴子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_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
