USE AdventureWorks
go

CREATE FUNCTION dbo.fnGetReports
       (@EmployeeID AS int)
        RETURNS @Reports TABLE
       (
        EmployeeID int not NULL,
        ManagerID int not NULL
        )
AS
BEGIN

/*Snce we'll need to this function recursively - that is once for each reporting
 ** emloyee (to make sure that they don't have reports of their own),
    we need a holding 
 ** variable to keep track of which employee we're currently working on.*/
 
DECLARE @Employee AS int

/*This inserts the current employee into our working table.The significance here
is
**that we need the first record as something of s primer due to the recursive nature
  of the function - this is how we get it.*/
INSERT INTO @Reports
   SELECT EmployeeID,ManagerID
   FROM HumanResources.Employee
   WHERE EmployeeID = @EmployeeID
/*Now we also need a primer for the recursive calls we're getting ready to start 
  making to this function .This would probably be better done with a cursor ,but we
  haven't gotten to that chapter yet,so...*/
SELECT @Employee = MIN(EmployeeID)
FROM HumanResources.Employee
WHERE ManagerID = @EmployeeID

/* This next part would probably be better done with a curson but we haven't gotten
to that chapter yet ,so we'll fake it .Notice the recursive call to our function!*/
WHILE @Employee IS NOT NULL
   BEGIN
      INSERT INTO @Reports
           SELECT *
           FROM fnGetReports(@Employee)
           
           SELECT @EmployeeID = MIN(EmployeeID)
           FROM HumanResources.Employee
           WHERE EmployeeID > @Employee
                 AND ManagerID = @EmployeeID
   END 
RETURN 

END

GO  

SELECT * FROM fnGetReports(12)