我试了一下,没问题,可以运行。
只是你贴出的程序有个错误
原:insert into @t_Level select * from find_sort(@in_id,@level,@sort)
新:insert into @t_Level select * from find_sort1(@in_id,@level,@sort) --应为find_sort1
create table area (a_id int,f_id int)
insert into area values(1,0)
insert into area values(2,0)
insert into area values(3,0)
insert into area values(4,1)
insert into area values(5,4)
create function find_sort1(@in_id int,@level int=1,@sort int=1)
returns @t_Level table(id int,t_level int,t_sort int)
as
begin
declare tb cursor local
for
select a_id from area where f_id=@in_id
open tb
fetch tb into @in_id
while @@fetch_status=0
begin
insert into @t_Level values(@in_id,@level,@sort)
set @level=@@nestlevel+1
set @sort=@sort+1
if @@nestlevel<32
begin
insert into @t_Level select * from find_sort(@in_id,@level,@sort)
set @level=@@nestlevel
set @sort=@sort+@@rowcount
end
fetch tb into @in_id
end
close tb
DEALLOCATE tb
return
end