--创建处理函数,查询指定id的所有子结点
create function f_id(@id int)
returns @re table(type_ID int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.type_ID,@l from Tree a join @re b on a.Parent_ID=b.type_ID where b.level=@l-1
end
return
end
go
--调用
declare @id int
select @id=10
select * from dbo.f_id(@id)