求在一张表中循环取数据的SQL
我有一张表的字段设计是
  id     Pid
    1       0
    2       1
    3       2
    4       2
    5       3
其中id   代表代号
        Pid   代表对应的父ID号
Pid为0的代表最顶，上面没有父ID号了  
比如   id   为2的父ID为1

我现在想问下一：如何通过SQL语句   输入一个ID号，找出它的子ID号和它本生的ID号
比如：我输入   2   ，输出结果为   2，3，4，5
                输入0，输出结果为0，1，2，3，4，5

------解决方案--------------------
create table tb(
id int not null,
pid int not null)
go
insert into tb select 1,0
union select 1,0
union select 2 ,1
union select 3,2
union select 4,2
union select 5,3
go
create function ge(@a int)
returns varchar(200) as
begin
declare @s varchar(200)
declare @i int
declare @p int
declare @cur cursor
set @s=Convert(varchar(20),@a)
select @i=count(*) from tb where pid=@a
if @i=0
return @s
set @p=@a
if @i> 0
begin

--游标操作
set @cur=cursor LOCAL SCROLL for select id from tb where pid=@p
open @cur
Fetch next from @cur into @P
while @@FETCH_STATUS=0
BEGIN
SET @S=@S+ ', '+DBO.GE(@P)
Fetch next from @cur into @P
END
close @cur
DEALLOCATE @cur

end
return @s
end
go
select dbo.ge(0)
select dbo.ge(2)
drop table tb
drop function ge
------解决方案--------------------
create table [Table](id int,pid int)
insert [Table] select 1, 0
union all select 2 ,1
union all select 3 ,2
union all select 4 ,2
union all select 5 ,3

go
create function fn_GetNode(@id int)
returns @a table(a int)
as
begin
declare @x table(a int)
declare @y table(a int)

insert @a select @id
insert @x select id from [Table] where pid=@id
insert @a select * from @x
while @@rowcount> 0
begin
insert @a select id from [Table] where pid in(select * from @x)
insert @y select id from [Table] where pid in(select * from @x)
delete from @x
insert @x select * from @y
delete from @y
end
return
end
go
declare @y varchar(100)
select @y=isnull(@y+ ', ', ' ')+ltrim(a) from dbo.fn_getnode(0) order by a
select @y