SQL中利用递归函数取树形结构的数据

表结构为：OrgDepartment

 @ID	<span style="white-space:pre">	</span>int
 @Proname	varchar(100)
 @FatherID	int

自定义函数为：

create function fun_GetName(@iID int)
returns @tbl table(ID int, Proname varchar(100), FatherID int)
begin

insert into @tbl select ID, Proname, FatherID from OrgDepartment where ID = @iID;

declare my_cursor cursor scroll dynamic
for
select ID from OrgDepartment where FatherID = @iID;
open my_cursor;

declare @ID int;

fetch next from my_cursor into @ID;
while(@@fetch_status=0)
begin
insert into @tbl select ID, Proname, FatherID from fun_GetName(@ID)
fetch next from my_cursor into @ID;
end

close my_cursor
deallocate my_cursor

return 
end

<span style="font-family: Arial, Helvetica, sans-serif;">select * from fun_GetName(3);</span>

结果：

ID	Proname	FatherID
3	a	0
42	b	3
43	c	42
48	d	42
46	e	3
47	f	46