获取树形节根节点下面所有层级子节点

--bom结构，查找节点下所有子节点：

create table Tree(DomainID uniqueidentifier,ParentID uniqueidentifier,desn varchar(10))
insert into Tree select '00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000000','A1'
insert into Tree select '00000000-0000-0000-0000-000000000002','00000000-0000-0000-0000-000000000001','A2'
insert into Tree select '00000000-0000-0000-0000-000000000003','00000000-0000-0000-0000-000000000002','A3'
insert into Tree select '00000000-0000-0000-0000-000000000004','00000000-0000-0000-0000-000000000003','A4'
insert into Tree select '00000000-0000-0000-0000-000000000005','00000000-0000-0000-0000-000000000000','B1'
insert into Tree select '00000000-0000-0000-0000-000000000006','00000000-0000-0000-0000-000000000005','B2'
insert into Tree select '00000000-0000-0000-0000-000000000007','00000000-0000-0000-0000-000000000006','B3'
insert into Tree select '00000000-0000-0000-0000-000000000008','00000000-0000-0000-0000-000000000007','C1'
insert into Tree select '00000000-0000-0000-0000-000000000009','00000000-0000-0000-0000-000000000008','C2'

select * from Tree

--求某个节点下所有子节点：
create function find_childid(@id uniqueidentifier)
returns varchar(500)
as
begin
     declare @t table(DomainID uniqueidentifier,ParentID uniqueidentifier,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  Tree where DomainID=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from Tree a,@t b
          where a.ParentID=b.DomainID and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(DomainID) from @t order by lev
     return @cids
end
go

--调用函数
select * from Tree
select *,ids=dbo.find_childid(DomainID) from Tree


--得到每个节点路径：
create proc GetNodeFullPath
@id uniqueidentifier
as
select *,cast(' ' as varchar(10)) fullpath  into #Tree from Tree
DECLARE @i int,@j int
set @i=0
set @j=1
select @i='00000000-0000-0000-0000-000000000009' from #Tree
update #Tree set fullpath=DomainID 
while @j<=@i
begin
       update #Tree set fullpath=a.fullpath+','+ltrim(#Tree.DomainID) 
            from #Tree inner join #Tree a on #Tree.ParentID=a.DomainID 
       where #Tree.ParentID=@j 
       set @j=@j+1
end
select * from #Tree
go
--调用存储过程
exec GetNodeFullPath '00000000-0000-0000-0000-000000000009'
--消息 245，级别 16，状态 1，过程 GetNodeFullPath，第 8 行
--在将 varchar 值 '00000000-0000-0000-0000-000000000009' 转换成数据类型 int 时失败。
--bom结构，查找节点下所有子节点：

create table Tree(Doma