*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 , --排序基數(shù)
userid int default 0 not null , --發(fā)言人id
forumid tinyint default 1 not null , --版面id
subject varchar(255) default '' not null , --主題
content text default '' not null , --內(nèi)容
faceid tinyint default 1 not null , --表情
hits int default 0 not null , --點(diǎn)擊數(shù)
ip varchar(20) default '' not null , --發(fā)貼ip
time datetime default getdate() not null , --發(fā)表時(shí)間
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 , --貼子總數(shù)
time datetime default getdate() not null , --創(chuàng)建時(shí)間
isopen bit default 0 not null --是否開放
)
go
/*************************************************************************/
/* */
/* procedure : up_gettopiclist */
/* */
/* description: 貼子列表 */
/* */
/* parameters: @a_intforumid : 版面id */
/* @a_intpageno: 頁號(hào) */
/* @a_intpagesize: 每頁顯示數(shù)，以根貼為準(zhǔn) */
/* */
/* 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
/*關(guān)閉計(jì)數(shù)*/
set nocount on
/*檢測(cè)是否有這個(gè)版面*/
if not exists(select * from forum where id = @a_intforumid)
return (-1)
/*求總共根貼數(shù)*/
select @introotrecordcount = count(*) from bbs where fatherid=0 and [email protected]_intforumid
if (@introotrecordcount = 0) --如果沒有貼子，則返回零
return 0
/*判斷頁數(shù)是否正確*/
if (@a_intpageno - 1) * @a_intpagesize > @introotrecordcount
return (-1)
/*求開始rootid*/
set @introwcount = (@a_intpageno - 1) * @a_intpagesize + 1
/*限制條數(shù)*/
set rowcount @introwcount
select @intbeginid = rootid from bbs where fatherid=0 and [email protected]_intforumid
order by id desc
/*結(jié)束rootid*/
set @introwcount = @a_intpageno * @a_intpagesize
/*限制條數(shù)*/
set rowcount @introwcount
select @intendid = rootid from bbs where fatherid=0 and [email protected]_intforumid
order by id desc
/*恢復(fù)系統(tǒng)變量*/
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
中國(guó)最大的web開發(fā)資源網(wǎng)站及技術(shù)社區(qū)，
