SQL中使用递归进行层级查找,层级的限制是23层

--SQL中使用递归进行层级查找,层级的限制是23层
GO
CREATE FUNCTION dbo.fnGetReports(@EmployeeID AS int)
       RETURNS @Reports TABLE
       (
       EmployeeID    int         NOT NULL,
       ManagerID   int         NULL
       )
AS
BEGIN

DECLARE @Employee AS int;

INSERT INTO @Reports
   SELECT EmployeeID, ManagerID 
   FROM HumanResources.Employee2 
   WHERE EmployeeID = @EmployeeID;

SELECT @Employee = MIN(EmployeeID) 
FROM HumanResources.Employee2
WHERE ManagerID = @EmployeeID;

WHILE @Employee IS NOT NULL
   BEGIN
      INSERT INTO @Reports
           SELECT * 
           FROM fnGetReports(@Employee);

           SELECT @Employee = MIN(EmployeeID) 
           FROM HumanResources.Employee2 
           WHERE EmployeeID > @Employee
              AND ManagerID = @EmployeeID;
   END

RETURN;

END
GO
--查找事例:查询id等于4的节点下的所有子节点
select * from dbo.fnGetReports(4)