Modify CTE to reference another field?

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.

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...

Here is my working code with em开发者_JS百科ployeeID, how can I get this to work with NTID (employee) and managerNTID (manager)

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  
)

Assuming that managerNTID references the manager's NTID column - try this:

CREATE FUNCTION fnGetEmployeeHierarchyNew(@EmployeeNTID int = null)  
RETURNS TABLE   
AS  
  RETURN   
  (  
    WITH NewCTE AS  
    (  
      SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
      FROM Employees  
      WHERE NTID = ISNULL(@EmployeeNTID, EmployeeNTID)  

      UNION ALL  

      SELECT e.EmployeeId, e.ManagerID, e.NTID, e.FullName--, e.Name  
      FROM Employees e  
      JOIN NewCTE y ON e.ManagerNTID = y.NTID  
    )  
    SELECT EmployeeId, ManagerID, NTID, FullName--, Name  
    FROM NewCTE
  )