You can do it.
-Create an infinite loop
Use cte (EmployeeID, ManagerID, Title, lev) as.
(
SELECT employee ID, manager ID, job, level = 1
Human resources department. employee
Where ManagerID is not empty.
Joint ownership
Select cte. Employee ID. Manager ID, cte. Title, cte.lev+ 1
From cte
Join human resources. Employee status is e.
In cte. ManagerID = e.EmployeeID
Among them, cte. lev < 3
)
-use MAXRECURSION to limit the recursion level to 2.
Select employee ID, manager ID and position.
From cte
go to
Note that I have an additional lev here, and the number of recursions is controlled by the value of lev.
2. Option (max recurrence 0);
Can be executed, but there is an infinite loop, and the last record number of the table has an infinite loop.
===》
That's because your recursion has no exit.