Jonathan Yong

In this part 2, I will perform the reverse of Step 1.
Given a employee, how do you find all his/her reporting managers (direct or indirectly) ?

Step 1 : Complete Part 1.

Step 2 : Create the following User-Defined Function:

CREATE FUNCTION fn_FindManagers (@InEmpId int)
RETURNS @retFindReports TABLE (emp_id int primary key,
   emp_name varchar(50) NOT NULL,
   mgrid int
)

/*Returns a result set that lists all the managers
  who the given employee reports to directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int

   -- table variable to hold accumulated results
   DECLARE @Managers TABLE (emp_id int primary key,
      emp_name varchar(50) NOT NULL,
      mgrid int,
      processed tinyint default 0)

 -- initialize @Managers with direct manager of the given employee
 INSERT @Managers
 SELECT emp2.emp_id, emp2.emp_name, emp2.emp_mgr, 0
 FROM employees,  employees emp2
 WHERE employees.emp_mgr=emp2.emp_id
 AND employees.emp_id = @InEmpId
 SET @RowsAdded = @@rowcount

 -- While new manager were added in the previous iteration
 WHILE @RowsAdded > 0
 BEGIN
  /*Mark all employee records whose manager are going
    to be found in this iteration with processed=1.*/
  UPDATE @Managers
  SET processed = 1
  WHERE processed = 0
 
  -- Insert manager who manager the employees marked 1.
  INSERT @Managers
  SELECT emp2.emp_id, emp2.emp_name, emp2.emp_mgr, 0
  FROM @Managers m, employees emp2
  WHERE m.mgrid=emp2.emp_id
  and emp2.emp_mgr <> emp2.emp_id and m.processed = 1
  SET @RowsAdded = @@rowcount
 
 
        /*Mark all employee records whose manager
    have been found in this iteration.*/
  UPDATE @Managers
  SET processed = 2
  WHERE processed = 1
 END

  
 if not exists(select * from @Managers m where m.emp_id in
    (select emp_id from employees where emp_id=emp_mgr))
 begin

    INSERT @Managers
    SELECT emp2.emp_id, emp2.emp_name, emp2.emp_mgr, 0
    FROM employees emp2
    WHERE emp2.emp_mgr = emp2.emp_id
    AND exists (select * from @Managers m where m.mgrid=emp2.emp_id)
   
 end

   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT emp_id, emp_name, mgrid
   FROM @Managers
   RETURN
END

You are now good to go :
In your SQL Query Analyzer, run the following query :

select * from fn_FindManagers(19).

Substitude 19 with whatever Employee No you want to test.

Labels: sql