title: Modify CTE to reference another field?
body: <p>I have the CTE below working well in re-cursing an employee table and building an expanded list of the employee id passed to it as well as all direct, and indirect reports.</p>
  
  <p>I'm trying to make a sister CTE to use user name instead of employee id, but I get no data back after I make the changes...</p>
  
  <p>Here is my working code with <code>employeeID</code>, how can I get this to work with <code>NTID</code> (employee) and <code>managerNTID</code> (manager)</p>
  
  <pre><code>CREATE FUNCTION fnGetEmployeeHierarchy   
  (    
    @EmployeeId int = null  
  )  
  RETURNS TABLE   
  AS  
  RETURN   
  (  
    WITH yourcte AS  
    (  
      SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
      FROM Employees  
      WHERE EmployeeId = isnull(@EmployeeId,EmployeeId)  
      UNION ALL  
      SELECT e.EmployeeId, e.ManagerID, e.NTID, e.FullName--, e.Name  
      FROM Employees e  
      JOIN yourcte y ON e.ManagerID = y.EmployeeId  
    )  
  SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
  FROM yourcte  
  )
  </code></pre>