Employee - Bosses

USE AdventureWorks;
GO
CREATE FUNCTION fnAllTheBosses
               (@EmployeeID INT)
RETURNS VARCHAR(512)
AS
  BEGIN
    DECLARE  @Bosses  AS VARCHAR(512)
    SET @Bosses = ''; 
    WITH EmployeeCTE(Name,EmployeeID,ManagerID)
         AS (SELECT FirstName + ' ' + LastName,
                    EmployeeID,
                    ManagerID
             FROM   HumanResources.Employee AS E
                    JOIN Person.Contact AS C
                      ON C.ContactID = E.ContactID
             WHERE  EmployeeID = @EmployeeID
             UNION ALL
             SELECT C.FirstName + ' ' + C.LastName,
                    HRE.EmployeeID,
                    HRE.ManagerID
             FROM   HumanResources.Employee AS HRE
                    JOIN Person.Contact AS C
                      ON C.ContactID = HRE.ContactID
                    JOIN EmployeeCTE AS E
                      ON E.ManagerID = HRE.EmployeeID)
    SELECT @Bosses = @Bosses + Name + '; '
    FROM   EmployeeCTE  
    RETURN @Bosses
  END
GO
-- Test UDF
SELECT dbo.fnAllTheBosses(250)
GO
-- Generate chain of command for all non-supervisory staff
WITH cteStaff([EmployeeName],EmployeeID)
     AS (SELECT [Name] = FirstName + ' ' + LastName,
                EmployeeID
         FROM   HumanResources.Employee HRE
                JOIN Person.Contact C
                  ON C.ContactID = HRE.ContactID
         WHERE  EmployeeID NOT IN (SELECT ManagerID
                                   FROM   HumanResources.Employee
                                   WHERE  ManagerID IS NOT NULL))
SELECT   [Employee & Bosses] = dbo.fnAllTheBosses(EmployeeID)
FROM     cteStaff
ORDER BY EmployeeName
GO