请教一个SQL存储过程的面试题
有一个表，结构为Tree(Child,Parent),写一个存储过程，将@inputnode的所有字节点放入一个Array并返回。

------解决方案--------------------

create table tree(child varchar(20),parent varchar(20))
insert tree
select '001 ', '000 ' union all
select '002 ', '001 ' union all
select '003 ', '001 ' union all
select '004 ', '002 ' union all
select '005 ', '002 ' union all
select '006 ', '003 '
go
create proc p(@inputnode varchar(20))
as
declare @i int
set @i=0
declare @re table(child varchar(20),parent varchar(20),level int)
insert into @re
select child,parent,@i from tree where child=@inputnode
while @@rowcount> 0
begin
set @i=@i+1
insert into @re
select a.child,a.parent,@i from tree a,@re b where a.parent=b.child and b.level=@i-1
end
select child from @re
go
exec p '001 '
drop table tree
drop proc p