tempTable

select * from cpe
select * from topoGroupTable
select * from topoTreeNodeTable


alter function

create function F_getAllParentGroupNameByGroupId(@groupId varchar(40))
returns varchar(500)
as
begin
return ''
end

drop function F_getAllParentGroupNameByGroupId



create function F_getAllParentGroupNameByGroupId(@groupId varchar(40))
returns varchar(500)
as
begin
declare @parentId varchar(40)
declare @parent varchar(500)
   set @parent=''
select @parentId=ParentId from topoTreeNodeTable where originId=@groupId
if(@parentId<>'TVNode=2' and @parentId<>'')
begin
set @parent=F_getAllParentGroupNameByGroupId(parentId)+'_'+@parent
end
return @parent
end

print(F_getAllParentGroupNameByGroupId('Server=100001,TVNode=103'))




SELECT * FROM employees WHERE BirthDate= (SELECT MAX(BirthDate) FROM employees WHERE BirthDate < (SELECT MAX(BirthDate) FROM employees))



create function GetChildren (@id varchar(20))
returns @t table(id varchar(20))
as
begin
    insert @t select wayid from tb where upperwayid = @id
    while @@rowcount > 0
        insert @t select a.wayid from tb as a inner join @t as b
        on a.upperwayid = b.id and a.wayid not in(select id from @t)
   return
end

drop function getAllParentGroupNameByGroupId
create function getAllParentGroupNameByGroupId(@originId varchar(50))
returns  varchar(500)
as begin
declare @t table(id varchar(50))
declare @tempParentId varchar(50)
declare @parent varchar(500)
insert @t select a.parentId from topoTreeNodeTable as a where a.originId = @originId
while @@rowcount>0
begin
insert @t select a.parentId from topoTreeNodeTable a  inner join @t as b
on b.id=a.originId and a.parentId not in(select id from @t)  and a.parentId not in( '','TVNode=2','TVNode=7')
end
select @parent=g.name from @t t,TopoGroupTable g where t.id=g.originId
return  @parent
end

select * from getAllParentGroupNameByGroupId('Server=100001,TVNode=105')
select * from topoTreeNodeTable
print(getAllParentGroupNameByGroupId('Server=100001,TVNode=105'))