Question

asp.net制作多级联动如何编写sql删除

表结构是这样的。如果制作多级的删除啊。
删除父类把它下面的分类（子类，子子类，子子子类）。删除。

ALTER PROCEDURE [dbo].[Zy_Category_Delete]
@CategoryId int
AS
DELETE [Category]
WHERE CategoryId=@CategoryId

DELETE [Category]
WHERE FatherId=@CategoryId

这样只能删除2级的。如果多级的呢？
怎么写

Answer 1

declare @taba TABLE([tabid] [int] NOT NULL)
insert @taba (tabid)
select CategoryId from Category WHERE FatherId=@CategoryId
declare @id int
while(exists(select top 1 * from @taba))--只要还有数据就继续循环
begin
select top 1 @id=tabid from @taba--从变量表中取出一条记录
insert @taba (tabid)--插入父ID等于@id这条记录的子记录
select CategoryId from Category WHERE FatherId=@id
delete from Category where CategoryId=@id--从原表中删除该记录
delete from @taba where tabid=@id--从变量表中删除该记录,因为已取到它的所有子记录不必要了
end
---这段可以适应无限级分类表

Answer 2

你这个到底有几级啊，可以用嵌套SQL语句啊,下面是两级实例

@CategoryId int
AS
DELETE [Category]
WHERE CategoryId IN ( SELECT CategoryId FROM Category WHERE FatherId = @CategoryId )