存储过程-树形结构数据删除

在项目中经常会遇到树形结构存储的数据，为了方便用户使用就会要求删除树的父节点同时子节点也跟着删除，这里根据网上查找的资料和自己的整理写了个存储过程，代码如下：

create PROCEDURE [dbo].[proc_EntityList] @EntityId varchar(36)
AS   
    BEGIN  
        SET NOCOUNT OFF  
        DECLARE @i INT  
        SET @i = 0  

        create table #FIDS --创建临时表#Tmp
        (
        id  varchar(36) not null, --创建列ID
        levelNo                varchar(50),      
        );

        INSERT  INTO #FIDS  
                ( id, levelNo )  
        VALUES  ( @EntityId, -- id - int  
                  1  -- levelNo - int  
                  )  
        WHILE @@ROWCOUNT > 0   
            BEGIN  
                SET @i = @i + 1  
                INSERT  #FIDS  
                        SELECT  F.ID ,  
                                @i + 1  
                        FROM    #FIDS R ,  
                                dbo.EntityList F  
                        WHERE   F.PID = R.id  
                                AND R.levelNo = @i  
            END  
              
        DELETE  FROM dbo.EntityList  
        WHERE   ID IN ( SELECT  ID  
                        FROM    #FIDS )  
    END  

 

转载于:https://www.cnblogs.com/xiangzhong/p/5028639.html