sql递归函数示例

create table t_tree(id int IDENTITY(1,1) ,parentid int,name varchar(10))
insert into t_tree select 0,''
insert into t_tree select 1,''
insert into t_tree select 1,''
insert into t_tree select 2,''
insert into t_tree select 3,''
insert into t_tree select 3,''
insert into t_tree select 5,''
insert into t_tree select 5,''
insert into t_tree select 5,''
go

create function get_child(@id int)
returns @child table (id int,parentid int,name varchar(10),level int)
as
begin
 declare @level int
 set @level=0
 insert into @child
 select *,@level from t_tree where id=@id
 while @@rowcount>0
 begin
  set @level=@level+1
  insert into @child
  select a.*,@level from t_tree a,@child b
  where b.id=a.parentid and b.level=@level-1
 end
 return
end
go

select * from get_child(1)