Пример синтаксиса CTE 
With EmployeeChart(EmployeeID, ManagerID, Title)
AS
(Select EmployeeID, ManagerID, Title
  FROM HumanResources.Employee
  WHERE EmployeeID = 3
  UNION ALL
SELECT L2.EmployeeID, L2. ManagerID, L2.Title
  FROM HumanResources.Employee AS L2
  JOIN EmployeeChart
    ON L2.ManagerID = EmployeeChart.EmployeeID)
Select * from EmployeeChart