create table bom2 ( id int, pid int, name varchar(10) ) create procedure proc_bom2 @id int as begin declare @temp_b table(id int, level int) declare @level int set @level = 1 insert into @temp_b select id,@level from bom2 where bom2.id=@id while @@rowcount >0 begin set @level = @level+1 insert into @temp_b select bom2.id,@level from bom2,@temp_b tb where bom2.pid=tb.id and tb.level=@level-1; end select bom2.* from bom2,@temp_b tb where bom2.id = tb.id; end exec proc_bom2 1 create function fun_bom2(@id int) returns @fun_temp_b table(id int,level int) as begin declare @level int set @level =1 insert into @fun_temp_b select id,@level from bom2 where bom2.id =@id while @@rowcount >0 begin set @level = @level +1 insert into @fun_temp_b select bom2.id,@level from bom2,@fun_temp_b tb where bom2.pid=tb.id and tb.level=@level-1 end return end select bom2.* from bom2,fun_bom2(1) b2 where b2.id=bom2.id