利用with关键字实现数据查询的递归调用

declare @sysCode nvarchar(50)

declare @re table(id nvarchar(50),[level] int)   

set @sysCode = '001';

with cte as (

select id,0 as [level] from T_SYS_Module

where code =@sysCode

union all

select t1.id,t2.[level]+1 from T_SYS_Module t1,cte t2

where t1.parentId=t2.id

)

insert  @re

select * from cte