SQL SERVER树型数据展示

--得到指定objectid的子id列表
CREATE     function DBO.f_getchildid(@id nvarchar(40))
returns @re table(objectid nvarchar(40))
as
begin
 insert into @re select objectid from account where parent_objectid=@id
 while @@rowcount>0
  insert into @re select a.objectid
   from account a inner join @re b on a.parent_objectid=b.objectid
   where a.objectid not in(select objectid from @re)
 return
end

--得到指定objectid的父ID列表
CREATE      function DBO.f_getparentid(@id nvarchar(40))
returns @re table(objectid nvarchar(40))
as
begin
 declare @pid nvarchar(40)
 select @pid=parent_objectid from account where objectid=@id
 while @pid<>@id
 begin
   insert into @re values(@pid)
   select @pid=parent_objectid,@id=objectid from account where objectid=@pid
 end
 return
end