create function f_getParent(@name varchar(10)) returns varchar(40) as begin declare @ret varchar(40) set @ret='' while exists(select 1 from tname where name=@name) select @ret=parent+@ret,@name=parent from tname where name=@name return @ret end go select 名称=name,祖先=dbo.f_getParent(name) from tname go
returns varchar(40)
as
begin
declare @ret varchar(40)
set @ret=''
while exists(select 1 from tname where name=@name)
select @ret=parent+@ret,@name=parent from tname where name=@name return @ret
end
go
select 名称=name,祖先=dbo.f_getParent(name) from tname
go