Searching Hierarchical data in database : Part 2
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
