sql 树形查询

declare @tree table(id nvarchar(50),lv int,pid varchar(max),tname nvarchar(50),pname nvarchar(max))
begin
declare @l int
set @l=0
insert @tree select [id],@l,[id],[DepName],[DepName]
from [DepartmentStructure] where ParentCode='123321' --and RecordStatus=1 --根目录
while @@rowcount>0
begin
set @l=@l+1
insert @tree select a.[id],@l,b.[pid]+','+a.[id],a.[DepName],b.[pname]+'/'+a.[DepName]
from [DepartmentStructure] a,@tree b
where a.[ParentCode]=b.[id] and b.lv=@l-1 --and RecordStatus=1  --子目录
end
end
select * from @tree