存储过程嵌套,递归出问题

[yj821]

只能删除当前设备及下一级设备不能删除下二级设备,不知是什么问题
declare @hh int
exec Sb_Del_Sb_All '01',@hh output

--删除设备信息
CREATE Procedure Sb_Del_Sb_All
(
@Sb_Bm varchar(50), --设备编码
@Fh_Value int output --返回是否成功,成功返回1,失败返回0
)
as
set nocount on
declare @into_error1 int
declare @Fh int
declare @Zsb_Fh int
begin tran
set @into_error1=0


if exists(select 1 from SB_TZ_SBM where FJD=@Sb_Bm ) --当存在子编码的情况
begin
SET CURSOR_CLOSE_ON_COMMIT OFF
declare test cursor for
select SBBM from SB_TZ_SBM where FJD=@Sb_Bm --选择所有的子编码
declare @Zsbbm varchar(50)
open test
fetch next from test into @Zsbbm
while @@FETCH_STATUS=0
begin

if exists(select 1 from SB_TZ_SBM where FJD=@Zsbbm ) --子编码下还存在子编码
begin
declare @S_sbbm varchar
declare @S_sbFh int
select @S_sbbm=SBBM from SB_TZ_SBM where SBBM=@Zsbbm
exec Sb_Del_Sb_All @S_sbbm,@S_sbFh output
if(@S_sbFh=0)
set @into_error1=1
end


exec Sb_Del_Sb @Zsbbm,@Zsb_Fh output --删除当前子编码
if(@Zsb_Fh=0)
set @into_error1=1
fetch next from test into @Zsbbm
end
close test
deallocate test

end



exec Sb_Del_Sb @Sb_Bm,@Zsb_Fh output --删除当前设备
if(@Fh=0)
set @into_error1=1


if(@into_error1=0)
begin
commit tran
set @Fh_Value=1
end
else
begin
set @Fh_Value=0
rollback tran
end
CREATE Procedure Sb_Del_Sb
(
@Sb_Bm varchar(50), --流程名称
@Fh_Value int output --返回是否成功,成功返回1,失败返回0
)
as
set nocount on
declare @into_error1 int
declare @into_error2 int
declare @Sb_id decimal
declare @DelJlSql varchar(200)
begin tran

set @into_error1=0
select @Sb_id=Id from SB_TZ_SBM where SBBM=@Sb_Bm
delete from SB_TZ_SBM where SBBM=@Sb_Bm --删除设备编码
set @into_error1=@@error

delete From SB_TZ_JCTZ Where SBBM=@Sb_Bm --删除设备基础台帐
set @into_error1=@@error

delete From SB_TZ_JSXN Where SBBM=@Sb_Bm --删除设备技术性能
set @into_error1=@@error

delete From SB_TZ_LBJ Where SBBM=@Sb_Bm --删除设备零部件
set @into_error1=@@error

delete From SB_TZ_PTFJ Where SBBM=@Sb_Bm --删除设备配套辅机
set @into_error1=@@error


delete From SB_Fj Where ModuleCode='SB_TZ_TCBG'
and ModuleId in(select Id from SB_TZ_TCBG Where SBBM=@Sb_Bm) --删除投产变更附件
set @into_error1=@@error

delete From SB_TZ_TCBGCS where TCBGID in --删除设备投产变更参数
(select Id from SB_TZ_TCBG Where SBBM=@Sb_Bm)
set @into_error1=@@error

delete From SB_TZ_TCBG Where SBBM=@Sb_Bm --删除设备投产变更
set @into_error1=@@error

delete From SB_TZ_JXJL Where SBBM=@Sb_Bm --删除设备检修经历
set @into_error1=@@error
delete From SB_Fj Where ModuleCode='SB_TZ_JXJL' and ModuleId=@Sb_id --删除检修经历的附件

if(@into_error1=0)
begin
commit tran
set @Fh_Value=1
end
else
begin
set @Fh_Value=0
rollback tran
end

[子陌红尘]

1、没有为变量@S_sbbm声明长度，导致变量类型默认为varchar(1)
-------------------------------------------------------------------------------------------------------------------
declare @S_sbbm varchar ==> declare @S_sbbm varchar(50)

2、以下操作只能获得当前子编码的下一级编码中的一个，即便是能正确执行，也不会将当前自编码的所有下级子编码全部删除掉；而直接将当前子编码传给Sb_Del_Sb_All即可达到预期目的。
-------------------------------------------------------------------------------------------------------------------
declare @S_sbbm varchar
declare @S_sbFh int
select @S_sbbm=SBBM from SB_TZ_SBM where SBBM=@Zsbbm
exec Sb_Del_Sb_All @S_sbbm,@S_sbFh output

==>

declare @S_sbFh int
exec Sb_Del_Sb_All @Zsbbm,@S_sbFh output

[yj821]

问题是不仅三级,可能有七级的

[浩方软件HFWMS]

你这样删除太麻烦了,而且效率也低,给你个例子,应该能满足你的要求:

[浩方软件HFWMS]

create proc DelNode
@id int
as
declare @FilterID int --左
declare @RightID int --右
declare @i int

delete from TableMenu where aid=@id
--if exists(select aid,topic_id from TableMenu where topic_id=@id)
select aid,topic_id from TableMenu where topic_id=@id
set @i=@@rowcount --节点扇出数
while @i>0
begin
select @FilterID=aid from TableMenu where topic_id=@id
--递归
exec DelNode @FilterID
--select @RightID=aid from TableMenu where topic_id=@id
--exec DelNode @RightID
set @i=@i-1
end

GO

/*
已经测试成功,你稍微改动一下就能用
*/
/*
功能:删除一个节点下的所有节点,
不仅是二叉节点(二叉树),任意树也行
*/

[浩方软件HFWMS]

给你付上表结构,自己输入数据测试一下吧,有点不想写了,呵呵~~
CREATE TABLE [TableMenu] (
[aID] [int] NOT NULL ,
[topic_id] [int] NOT NULL ,
CONSTRAINT [PK_TableMenu] PRIMARY KEY CLUSTERED
(
[aID],
[topic_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

[yj821]

谢谢lsp69(浪子神剑) 这种方法应该可以实现
感觉还是少用游标好