无限级联删除region下的区域以及下属二级区域(sqlserver 存储过程)

/***************************************************************
//    存储过程名称： [u_region_nodedelete]
//    功能描述： 无限级联删除region下的区域以及下属二级区域
//    作    者： czy
//
//    修改记录
// ---------------------------------------------------------------
//    修改日期    版本号        修改人        修改摘要
// ---------------------------------------------------------------
//    2013-10-25    V1.0     czy        无限级联删除
// ***************************************************************/
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    CREATE PROCEDURE u_region_nodedelete  
        @id BIGINT  
    AS  
    BEGIN  
        DECLARE @tbIds TABLE(id BIGINT)  
        DECLARE @tempTbs TABLE(id BIGINT)  
        DECLARE @tb TABLE(id BIGINT,orderIndex BIGINT IDENTITY(1,1))  
        INSERT INTO @tbIds(id) VALUES(@id)  
        INSERT INTO @tempTbs(id) VALUES(@id)  
        INSERT INTO @tb(id) VALUES(@id)  
        WHILE EXISTS(SELECT id FROM @tbIds)  
            BEGIN  
                DELETE FROM @tbIds  
                INSERT INTO @tb SELECT regid FROM region WHERE up_regid IN (SELECT ID FROM @tempTbs)  
                INSERT INTO @tbIds SELECT regid FROM region WHERE up_regid IN (SELECT ID FROM @tempTbs)  
                DELETE FROM @tempTbs  
                INSERT INTO @tempTbs SELECT id FROM @tbIds  
            END  
        DECLARE @tid INT   
        DECLARE myCursor CURSOR FOR SELECT id FROM @tb ORDER BY orderIndex DESC   
        OPEN myCursor   
        FETCH NEXT FROM myCursor INTO @tid   
        WHILE @@fetch_status=0   
        BEGIN  
            DELETE FROM region WHERE regid = @tid
            DELETE FROM regionpre WHERE regid = @tid
            FETCH NEXT FROM myCursor INTO @tid    
        END  
        CLOSE myCursor     
        DEALLOCATE myCursor  
    END  
    GO