表结构为: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