删除无限极分类的sql存储过程！

CREATE PROCEDURE [dbo].[Category_Delete]
@CategoryId int
 AS 
	--DELETE [Category]
	-- WHERE CategoryId=@CategoryId 

	--DELETE [Category]
	-- WHERE FatherId=@CategoryId 

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
     DELETE [Category]
	 WHERE CategoryId=@CategoryId 
---这段可以适应无限级分类表 

表设计：

 

转载于:https://www.cnblogs.com/ret00100/archive/2010/08/03/1791041.html