简单自关联表中，查找所有下级数据的函数

if exists (select * from sys.objects where name=‘func_all_related_levels‘)
    drop function func_all_related_levels
go
create function func_all_related_levels (
    @id int
)
returns @result table (
    id int
)
as
begin
    ------第一次插入数据。
    declare @total int;
    select @total = count(*) from TestTable where PId = @id
    if @total = 0
        return;
    insert @result
        select Id from TestTable where PId = @id;
    ------遍历，可用。
    declare @cursor int = 0;
    while @cursor < @total
        begin
            declare @nextId int;
            select @nextId = id from @result order by id offset (@cursor) rows fetch next 1 rows only;
            insert @result
                select id from dbo.func_all_related_levels(@nextId);
            set @cursor = @cursor + 1;
        end

    ------游标，出现多余数据。
    --declare c cursor for select id from @result;
    --open c;
    --while @@FETCH_STATUS = 0
    --    begin
    --        declare @nextId int;
    --        fetch next from c into @nextId;
    --        insert @result
    --            select id from dbo.func_all_related_levels(@nextId);
    --    end
    --close c;
    --deallocate c;
    return;
end