CREATE FUNCTION AWEmpTree(@startAt INT)
RETURNS TABLE
RETURN
WITH Rollup AS (
SELECT EmployeeID, ManagerID,
	0 AS depth
	FROM AdventureWorks.HumanResources.Employee
	WHERE EmployeeID = @startAt
UNION ALL
SELECT E.EmployeeID,
	E.ManagerID, depth + 1 AS depth
	FROM AdventureWorks.HumanResources.Employee E
	JOIN
	Rollup AS R ON R.EmployeeID = E.ManagerID 
)
SELECT EmployeeID, ManagerID, depth FROM Rollup
a
b
c
