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