删除无限极分类的sql存储过程

最新推荐文章于 2022-05-31 19:25:29 发布

ddqt79136

最新推荐文章于 2022-05-31 19:25:29 发布

阅读量88

点赞数

原文链接：http://www.cnblogs.com/thinkfate/archive/2012/08/15/2640128.html

版权

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