树形结构取得所有子父结点函数

/*取得子结点*/

create function fn_getSon(@parentid int)
returns @temptab table(id int,level int)
as
begin
 declare @level int
 set @level=1
 insert into @temptab(id,level) values(@parentId,@level)
 while @@rowcount>0
 begin
  SET @Level=@Level+1
  INSERT @temptab SELECT a.id,@Level
  FROM organise a,@temptab b
  WHERE a.parentId=b.id
       AND b.Level=@Level-1
 end
  

 return
end

go

/*取得父结点*/

create function fn_getPar(@sonid int)
returns @temptab table(id int,level int)
as
begin
 declare @level int
 set @level=1
 insert into @temptab(id,level) values(@sonId,@level)
 while @@rowcount>0
 begin
  SET @Level=@Level+1
  INSERT @temptab SELECT a.parentid,@Level
  FROM organise a,@temptab b
  WHERE a.Id=b.id
       AND b.Level=@Level-1
 end
  

 return
end

go

declare @ta table(单位ID int,  单位名称 varchar(5), ParentID int)
insert @ta select 1,        'AAA',         0
insert @ta select 2,        'BBB',         1
insert @ta select 3,        'CCC',         1
insert @ta select 4,        'DDD',         1
insert @ta select 5,        'EEE',         2
insert @ta select 6,        'EEE',         5
select space(parentid*5)+单位名称 from @ta

go