存储过程 高手请进

[hebeiman]

id Parent_id name
1 0 sdf
2 1 sdf
3 1 sdf
4 2 sdf
5 2 sdf
6 3 asdf
7 3 asdf

有意上表
假如删除1的话 那么必须删除 parent_id 是1的 那么就会删除 2，3 。 2，3删除了及必须删除 parent_id 是 2，3 的 以此类推
请高手写出 存储过程
输入参数 为 编号 id

[hebeiman]

咋没人恢复呀 我顶

[Functions]

首先删除pid = 1
然后使用循环删除，PID不在ID里面的（PID=0的除外），行不行？
delete t1

insert into t1
select 1, 0, 'sdf'
union
select 2, 1, 'sdf'
union
select 3, 1, 'sdf'
union
select 4, 2, 'sdf'
union
select 5, 2, 'sdf'
union
select 6, 3, 'asdf'
union
select 7, 3, 'asdf'
go

select * from t1
go

delete from t1 where pid = 1
while @@rowcount>0
delete from t1 where pid not in (select id from t1 ) and pid <> 0

[pbsql]

用触发器：
CREATE TRIGGER tr_delete ON dbo.t1
FOR DELETE
AS
delete from t1 where Parent_id in (select id from deleted)

[zjcxc]

create proc p_delete
@id int --要删除的id
as
create table #t(id int,level int)
declare @l int
set @l=0
insert #t select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.id,@l
from 表 a join #t b on a.Parent_id=b.id
where b.level=@l-1
end
delete a
from 表 a join #t b on a.id=b.id
go

--调用
exec p_delete 2

[qizhanfeng]

学习