Listing 3: Creation Script for fn_getsubtree Function

USE Northwind;
GO
IF OBJECT_ID('dbo.fn_getsubtree') IS NOT NULL
  DROP FUNCTION dbo.fn_getsubtree;
GO
CREATE FUNCTION dbo.fn_getsubtree(@root AS INT)
  RETURNS TABLE
AS
RETURN
  WITH EmpCTE
  AS
  (
    SELECT
      EmployeeID AS empid,
      FirstName + ' ' + LastName AS empname,
      0 AS lvl
    FROM dbo.Employees
    WHERE EmployeeID =  @root

    UNION ALL

    SELECT
      E.EmployeeID,
      E.FirstName + ' ' + E.LastName,
      M.lvl + 1
    FROM dbo.Employees AS E
      JOIN EmpCTE AS M
        ON E.ReportsTo = M.empid
  )
  SELECT * FROM EmpCTE;
GO