更新后的存储过程，散分容易结贴难

[aoeiuvcom]

因此，就不再给分，此贴沉了就让它沉了

希望对某些人有点帮助，也希望能得到一些指点（要分则另开贴）

---------------------------------------------------------------------
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 int
declare @nr_ID int
declare @nr_No int
declare @nr_Order int
declare @nr_OrderDesc int
declare @nr_Indent int
declare @nt_Timer int
declare @p1 int
declare @p2 int
declare @msg nvarchar(255)

-- 取消计数
set nocount on

-- 是否存在主题贴并可回复
set @msg = '找不到要回复的主题帖'
Select @ID_Club=ID_Club, @nr_No=nt_Reply
from a_tblNoteList where nt_ID=@ID_Note and nt_Del=0 and nt_Lock=0
if (@@rowcount = 0) goto OnMsg

-- 涉及多表操作，开始事务
BEGIN TRANSACTION

-- 最新的回复数
set @nr_No = @nr_No + 1

if @nr_Reply = 0 -- 回复主题贴
begin
set @nr_Order = @nr_No
set @nr_OrderDesc = 1
set @nr_Indent = 1
-- 插入
Update a_tblNoteReply set nr_OrderDesc=nr_OrderDesc+1 where ID_Note=@ID_Note
if (@@error != 0) goto OnError -- 判断是否成功
end
else -- 回复非主题贴
begin
set @msg = '找不到要回复的帖子'
-- 获取被回复贴的信息
select @nr_Order=nr_Order, @nr_Indent=nr_Indent, @nr_OrderDesc=nr_OrderDesc
from a_tblNoteReply where ID_Note=@ID_Note and nr_No=@nr_Reply and nr_Del=0
if (@@rowcount = 0) goto OnError -- 判断是否没有或者被删

-- 查找下一个同缩进的回复
set rowcount 1
select @nr_Order=nr_Order from a_tblNoteReply
where ID_Note=@ID_Note and nr_Indent<=@nr_Indent and nr_Order>@nr_Order
order by nr_Order
if (@@rowcount = 0)
begin
set @nr_Order = @nr_No
end
else
begin
-- 插入
Update a_tblNoteReply set nr_Order=nr_Order+1
where ID_Note=@ID_Note and nr_Order>=@nr_Order
if (@@error != 0) goto OnError -- 判断是否成功
end

-- 插入，后来居上
Update a_tblNoteReply set nr_OrderDesc=nr_OrderDesc+1
where ID_Note=@ID_Note and nr_OrderDesc>@nr_OrderDesc
if (@@error != 0) goto OnError -- 判断是否成功

-- 缩进
set @nr_OrderDesc = @nr_OrderDesc + 1
set @nr_Indent = @nr_Indent + 1
end


set @msg = '未知错误，请稍后再回复'

-- 得到最新的时间ID号
Select @nt_Timer=inf_Num from a_tblWebInfo where ID=@NextTimerID
Update a_tblWebInfo Set inf_Num=inf_Num+1,inf_Time=getdate() where ID=@NextTimerID
if (@@error != 0) goto OnError -- 判断是否成功

set @nt_Timer = @nt_Timer + 1

-- 更新主题贴
Update a_tblNoteList Set nt_Timer=@nt_Timer, nt_Reply=nt_Reply+1, nt_TimeReply=getdate(),name_Reply=@mem_Name
Where nt_ID=@ID_Note
if (@@error != 0) goto OnError -- 判断是否成功

-- 更新Club信息
Update a_tblClub Set clb_Update=getdate(),clb_Reply=clb_Reply+1 where clb_ID=@ID_Club
if (@@error != 0) goto OnError -- 判断是否成功

-- 更新用户信息
Update a_tblMember Set num_Reply=num_Reply+1 where mem_ID=@ID_Member
if (@@error != 0) goto OnError -- 判断是否成功

-- 最新回复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
if (@@error != 0) goto OnError -- 判断是否成功

INSERT INTO a_tblNoteReply (
ID_Note, nr_ID, ID_Club, nr_No, nr_Order, 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_Order, @nr_Indent, @nr_Reply, 0, @ID_Member,
@nr_PostIP, @nr_Size, @nr_Face, 0, @nr_HTML, 0, getdate(), getdate(), @nr_Data)

if (@@error != 0) goto OnError -- 判断是否成功

set @msg = ''
-- 结束事务
COMMIT TRANSACTION
set rowcount 0
set nocount off
select 'msg' = @msg
return(0)

-- 错误处理
OnError:
rollback transaction
OnMsg:
set rowcount 0
set nocount off
select 'msg' = @msg
return(-1)

END
GO

[odesseydk]

!!!!!!