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
