这是一个无限级菜单的数据结构 id int topic_id int msg varchar(30) topic_id对应同一个表中已经存在的ID,如果为0表示根节点 现在想要删除关联的数据,删除指定id纪录的同时要删除topic_id指向这个id的纪录,以及被删除的子节点的关联节点,即topic_id指向子节点的纪录,如此反复 传入的id可能是数字,也可能是形如“0,1,2,3”这样的字符串 谢谢
create table TableMenu(aID int,topic_id int)
--测试数据
insert into TableMenu select 1,0
insert into TableMenu select 2,1
insert into TableMenu select 3,1
insert into TableMenu select 4,2
insert into TableMenu select 5,2
insert into TableMenu select 6,3
insert into TableMenu select 7,4
insert into TableMenu select 8,3
insert into TableMenu select 9,7
go
create proc DelNode
@id int
as
declare @FilterID int
delete from TableMenu where aid=@id
if exists(select aid,topic_id from TableMenu where topic_id=@id)
begin
select @FilterID=aid from TableMenu where topic_id=@id
--递归
exec DelNode @FilterID
end
go
create proc DelMenu
@idi int
as
declare @id int
declare @topic varchar(10)
declare Del_Cursor cursor local for
select idi,topic_id from table1 where idi=@idi
open Del_Cursor
fetch next from Del_Cursor
into @id,@topicid
while @@fetch_status=0
begin
delete from table1 where idi=@id
delete from table1 where topic_id=@topic_id
fetch next from Del_Cursor
into @id,@topicid
end
close Del_Cursor
deallocate Del_Cursor
go
-- 或者二
Delect From Table1 Where ID = @ID Or Topic_ID = @ID
create table tb(ID int,topic_id int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,2
insert into tb select 6,3
insert into tb select 7,3
go
--创建用户定义函数
create function f_getChild(@ID int)
returns @tb table(ID int,topic_id int)
as
begin
insert @tb select ID,topic_id from tb where topic_id = @ID
while @@rowcount<>0
begin
insert @tb
select
a.ID,a.topic_id
from
tb a,@tb b
where
a.topic_id=b.ID
and
not exists(select 1 from @tb where ID=a.ID)
end
return
end
go
--创建存储过程
create procedure deleteNodes
@IDList varchar(1000) --要删除的id列表
as
declare @tb table
(
ID int,
num int identity
)
while charindex(',',@IDList)>0
begin
insert @tb(ID) select left(@IDList,charindex(',',@IDList)-1)
set @IDList=stuff(@IDList,1,charindex(',',@IDList),'')
end
insert @tb(ID) select @IDList
set xact_abort on
begin tran
declare @i int,@tmp int
set @i=1
while @i<=(select max(num) from @tb)
begin
select @tmp=ID from @tb where num=@i
delete from tb where exists(select 1 from f_getChild(@tmp) where ID=tb.ID)
set @i=@i+1
end
--生成测试数据
create table BOM(ID INT,Topic_ID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),Topic_ID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,Topic_ID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Topic_ID,@i
from
BOM a,@t b
where
a.Topic_ID=b.ID and b.Level = @i-1
end
return
end
go
--创建用户定义存储过程
create procedure sp_relationDel(@var varchar(100))
as
begin
declare @ID varchar(100)
while charindex(',',@var)>0
begin
set @ID = left(@var,charindex(',',@var)-1)
set @var = stuff(@var,1,charindex(',',@var),'')
delete BOM where ID in(select ID from dbo.f_getChild(@ID))
end
delete BOM where ID in(select ID from dbo.f_getChild(@var))
end
go
参考一下这个用户定义函数:
---------------------------------------------------------
--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go