递归删除数据

 

/*
    删除某个产品目录，一并删除其子目录
    @CatalogId：要删除的节点编号
*/
Create Proc DeleteProcuctCatalog
@CatalogId Int
As
--影响的行
Declare @State Int

--改变当前目录的删除状态
Update tProductCatalog Set DeleteFlag = 1 Where ProductCatalogID = @CatalogId And DeleteFlag = 0
Set @State = (Select @@RowCount)

If @State = 0
    return;

--把子节点插入临时表中 
Create Table #tmp
(
    ProductCatalogID int, 
    ParentCatalogID int,
    DeleteFlag tinyint,
    New bit default 1
)
Insert Into #tmp (ProductCatalogID, ParentCatalogID, DeleteFlag) (Select ProductCatalogID, ParentCatalogID, DeleteFlag From dbo.tProductCatalog Where ParentCatalogID = @CatalogId)

--修改所有子节点的删除状态
While @State != 0
Begin

    Update tProductCatalog Set DeleteFlag = 1 Where ProductCatalogID In (Select ProductCatalogID From #tmp Where DeleteFlag = 0)
    
    --保存所有节点的子节点
    Update #tmp Set New = 0
    Insert Into #tmp (ProductCatalogID, ParentCatalogID, DeleteFlag) (Select ProductCatalogID, ParentCatalogID, DeleteFlag From dbo.tProductCatalog Where ParentCatalogID In (Select ProductCatalogID From #tmp Where New = 0))
    Delete #tmp Where New = 0
    Set @State = (Select Count(*) From #tmp)
End

Drop Table #tmp