SQL 函数：树结构指定父节点遍历所有的子节点

CREATE function [dbo].[Get_DepChildren]
(
    @ID int
)
RETURNS @Tree Table (ID [int] IDENTITY (1, 1),PID  Int,FID Int,SN Varchar(50), Name Varchar(150))
as
begin 
--declare @ID Int
--set @ID=71
--declare @Tree Table (AID Int,pid Int, fid Int,sn Varchar(200), Name Varchar(200))
Insert @Tree Select  pid, fid, sn, Name  From tbDepList Where pid = @ID
While (@@Rowcount > 0) 
  Begin
    Insert @Tree Select  A.pid, A.fid, A.sn, A.Name From tbDepList A 
    Inner Join @Tree B On A.fid = B.pid And A.pid Not In (Select pid From @Tree)
  End
Return
end 
--Select * from  dbo.Get_DepChildren(11) 

GO