删除无限极分类的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