*bbs表*/
if exists(select * from sysobjects where id = object_id('bbs'))
drop table bbs
go
create table bbs
(
id int identity primary key ,
rootid int default 0 not null , --根id
fatherid int default 0 not null , --父id
layer tinyint default 0 not null , --层
ordernum float(53) default 0 not null , --排序基数
userid int default 0 not null , --发言人id
forumid tinyint default 1 not null , --版面id
subject varchar(255) default '' not null , --主题
content text default '' not null , --内容
faceid tinyint default 1 not null , --表情
hits int default 0 not null , --点击数
ip varchar(20) default '' not null , --发贴ip
time datetime default getdate() not null , --发表时间
posted bit default 0 not null --是否精华贴子
)
go
/*forum版面表*/
if exists(select * from sysobjects where id = object_id('forum'))
drop table forum
go
create table forum
(
id tinyint identity primary key ,
rootid tinyint default 0 not null , --根id
fatherid tinyint default 0 not null , --父id
layer tinyint default 0 not null , --层
title varchar(50) default '' not null , --版面名称
description varchar(255) default '' not null , --版面描述
masterid int default 1 not null , --版主id
topiccount int default 0 not null , --贴子总数
time datetime default getdate() not null , --创建时间
isopen bit default 0 not null --是否开放
)
go
/*************************************************************************/
/* */
/* procedure : up_gettopiclist */
/* */
/* description: 贴子列表 */
/* */
/* parameters: @a_intforumid : 版面id */
/* @a_intpageno: 页号 */
/* @a_intpagesize: 每页显示数，以根贴为准 */
/* */
/* use table: bbs , forum */
/* */
/* author: [email protected] */
/* */
/* date: 2000/2/14 */
/* */
/* history: */
/* */
/*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_gettopiclist'))
drop proc up_gettopiclist
go
create proc up_gettopiclist
@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 fatherid=0 and [email protected]_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 fatherid=0 and [email protected]_intforumid
order by id desc
/*结束rootid*/
set @introwcount = @a_intpageno * @a_intpagesize
/*限制条数*/
set rowcount @introwcount
select @intendid = rootid from bbs where fatherid=0 and [email protected]_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 [email protected]_intforumid and a.rootid between @intendid and @intbeginid
order by a.rootid desc , a.ordernum desc
return(@@rowcount)
--select @@rowcount
go
中国最大的web开发资源网站及技术社区，
