无限级删除的存储过程

CREATE PROCEDURE [dbo].[Delete_Part]
 
 @CategoryId int

  AS

     --DELETE [Category]

     -- WHERE ID=@CategoryId 

  

     --DELETE [Category]

     -- WHERE Higher=@CategoryId 

  

 declare @taba TABLE([tabid] [int] NOT NULL)

 insert @taba (tabid)

 select ID from ZN_Part WHERE Higher=@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 ID from ZN_Part WHERE Higher=@id

        delete from ZN_Part where ID=@id--从原表中删除该记录

        delete from @taba where tabid=@id--从变量表中删除该记录,因为已取到它的所有子记录不必要了

      end

      DELETE [ZN_Part]

      WHERE ID=@CategoryId 

 ---这段可以适应无限级分类表

GO