SQL Server 2005 UDF to make a table data-type of self referencing table data

Question

I have a typical self-referencing table of employees.

How can you build an UDF to return a table data-type that can be used in other queries to join to such that I pass the UDF a id of a user in the table and get a result of that user's id and all users linked to him via a managerId?

The table I have is EmployeeId, ManagerID, Name....

All I need is to pass an EmployeeId and get a recursive result of all records who's ManagerID is the id of the passed in param, and any of the records that have these as managers, and so on...

thanks

Answer 1

CREATE FUNCTION GetEmployees  
(  
  @EmployeeId int
)
RETURNS TABLE 
AS
RETURN 
(
  WITH yourcte AS
  (
    SELECT EmployeeId, ManagerID, Name
    FROM Employees
    WHERE EmployeeId = @EmployeeId
    UNION ALL
    SELECT e.EmployeeId, e.ManagerID, e.Name
    FROM Employees e
    JOIN yourcte y ON e.ManagerID = y.EmployeeId
  )
SELECT EmployeeId, ManagerID, Name
FROM yourcte
)