论坛添加回复的存储过程,第一次写,竟然顺利通过,散分:)
命名可能不符合某些人的“胃口”,我只是为了能跟数据库能很好的联系起来:)
对于正在写的论坛,分页已经用了存储过程,现在打算把其他复杂的也放入存储过程,
这样做的好处就是:ASP页面好简单呀!
不知道有什么坏处?
整个数据库里没有一个是自动累加的字段,都是通过某个表,每次用时取出来+1
这样,整个数据库想怎么改就怎么改,想删想加,不用再为ID号发愁了:)
已经使用存储过程的有:
1、顶贴列表
2、主题贴分页
3、读取主题帖
4、读取回复贴并分页
5、添加回复
正在做的有:
1、添加主题贴
打算做的有:
1、用户登陆验证
2、修改帖子
3、论坛传呼
希望对存储过程有经验的朋友能给点提示或者帮助,该加强什么或者回避什么:)
我的网站:http://www.aoeiuv.com
希望能在短期内放上自己写的论坛:)
------------------------------------------------------------
CREATE PROCEDURE [dbo].[cmdClubInsertReply]
(
@NextTimerID int,
@NextReplyID int,
@ID_Member int,
@mem_Name nvarchar(32),
@nr_PostIP int,
@ID_Note int,
@nr_Reply smallint,
@nr_Face tinyint,
@nr_HTML tinyint,
@nr_Size int,
@nr_Data ntext
)
AS
BEGIN
declare @ID_Club smallint
declare @nr_ID int
declare @nr_No smallint
declare @nr_OrderNo smallint
declare @nr_Indent smallint
declare @p1 int
declare @p2 int
set nocount on
-- 是否存在主题贴
if not exists(Select nt_ID from a_tblNoteList where nt_ID=@ID_Note and nt_Del=0)
return (-1)
Select @ID_Club=ID_Club, @p1=nt_Lock, @p2=nt_Reply from a_tblNoteList where nt_ID=@ID_Note
-- 判断是否被设为禁止回复
if @p1 > 0
return (-1)
set @nr_No = @p2 + 1
if @nr_Reply > 0 -- 回复非主题贴
begin
-- 检查是否被删
if not exists(select nr_ID from a_tblNoteReply where ID_Note=@ID_Note and nr_No=@nr_Reply and nr_Del=0)
return (-1)
select @nr_OrderNo=nr_OrderNo, @nr_Indent=nr_Indent from a_tblNoteReply where ID_Note=@ID_Note and nr_No=@nr_Reply
-- 插入
Update a_tblNoteReply set nr_OrderNo=nr_OrderNo+1 where ID_Note=@ID_Note and nr_OrderNo>=@nr_OrderNo
-- 缩进
set @nr_Indent = @nr_Indent + 1
end
else
begin
set @nr_OrderNo = @p2 + 1
set @nr_Indent = 1
end
-- 得到最新的时间ID号
Select @p1=inf_Num from a_tblWebInfo where ID=@NextTimerID
Update a_tblWebInfo Set inf_Num=inf_Num+1,inf_Time=getdate() where ID=@NextTimerID
set @p1 = @p1 + 1
-- 更新主题贴
Update a_tblNoteList Set nt_Timer=@p1, nt_Reply=nt_Reply+1, nt_TimeReply=getdate(),name_Reply=@mem_Name
Where nt_ID=@ID_Note
-- 更新Club信息
Update a_tblClub Set clb_Update=getdate(),clb_Reply=clb_Reply+1 where clb_ID=@ID_Club
-- 更新用户信息
Update a_tblMember Set num_Reply=num_Reply+1 where mem_ID=@ID_Member
-- 最新回复ID
Select @p1=inf_Num from a_tblWebInfo where ID=@NextReplyID
Update a_tblWebInfo Set inf_Num=inf_Num+1,inf_Time=getdate() where ID=@NextReplyID
set @nr_ID = @p1 + 1
set nocount off
INSERT INTO a_tblNoteReply (ID_Note ,
nr_ID ,
ID_Club ,
nr_No ,
nr_OrderNo ,
nr_Indent ,
nr_Reply ,
nr_Del ,
ID_Member ,
nr_PostIP ,
nr_Size ,
nr_Face ,
nr_Lock ,
nr_HTML ,
nr_Type2 ,
nr_TimePost ,
nr_TimeModify ,
nr_Data)
VALUES (@ID_Note ,
@nr_ID ,
@ID_Club ,
@nr_No ,
@nr_OrderNo ,
@nr_Indent ,
@nr_Reply ,
0 ,
@ID_Member ,
@nr_PostIP ,
@nr_Size ,
@nr_Face ,
0 ,
@nr_HTML ,
0 ,
getdate() ,
getdate() ,
@nr_Data
)
END
GO