Create proc getY(@id int)
as
declare @a table(id int, name nvarchar(30), parent_id int)
insert @a select 1 ,'pp1_1', 0
union all select 2 ,'pp2_1', 0
union all select 3 ,'pp3_2', 1
union all select 4 ,'pp4_2', 2
union all select 5 ,'pp5_3', 3
union all select 6 ,'pp6_4', 5
if object_id('tab') is not null
drop table tab
declare @s varchar(1000)
declare @v varchar(1000)
declare @I int
declare @L int
set @l=0
set @I=@id
set @s='Create table tab('
set @v='Insert into tab select '
while exists(select 1 from @a where id=@id)
begin
select @s=@s+'a'+ltrim(@l)+' int,'
select @v=@v+ltrim(@id)+','
select @id=parent_id from @a where id=@id
set @l=@l+1
end
if @l=0
begin
select '无' as '无'
return
end
select @s=left(@s,len(@s)-1)+')'
exec(@s)
select @v=left(@v,len(@v)-1)
exec(@v)
select * from tab