SqlServer里，一条sql进行递归删除

Server 2005中提供了公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。 

 

存储过程方法：

create proc up_delete_nclass
@did int 
as
with my1 as(
    select * from News_Class where id = @did
    union all 
    select News_Class.* from my1, News_Class where my1.id = News_Class.ParentID
)
delete from News_Class where exists (select id from my1 where my1.id = News_Class.id) 
go
exec up_delete_nclass 16

　　

　　

非存储过程方法：

with my1 as(
    select * from aaa where id = 1
    union all 
    select aaa.* from my1, aaa where my1.id = aaa.pid
)
delete from aaa where exists (select id from my1 where my1.id = aaa.id)