If your database has a table that keep track of employees and their reporting
manager, given an employee how can you can out all the employees who report
(direct or indirectly) to this particular employee ?
I will show you how to accomplish this using SQL Server 2000 User-Defined function.
This technique is applicable if you need to find hierarchical data stored in a table.
Step 1 : create the following Employees table :
CREATE TABLE [dbo].[Employees] (
[emp_id] [int] NOT NULL ,
[emp_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emp_mgr] [int] NULL
) ON [PRIMARY]
GO
Step 2 : Insert the following data into the Employees table :
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (1, 'ceo', 1)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (2, 'cio', 1)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (3, 'cfo', 1)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (4, 'coo', 1)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (5, 'app manager', 2)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (6, 'infra manager', 2)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (7, 'helpdesk manager', 2)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (8, 'purchasing manager', 3)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (9, 'account manager', 3)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (10, 'hr manager', 4)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (11, 'training manager', 4)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (12, 'sap engineer', 5)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (13, 'dev lead', 5)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (14, 'developer', 5)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (15, 'IIS admin', 6)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (16, 'AD admin', 6)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (17, 'Security Engineer', 6)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (18, 'Account Asst', 9)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (19, 'Purchasing Officer 1', 8)
INSERT INTO Employees(emp_id, emp_name, emp_mgr)
values (20, 'Purchasing Office 2', 8)
Step 3 : Create the following User-Defined Function:
CREATE FUNCTION fn_FindReports (@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
employees who report to given employee
directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (emp_id int primary key,
emp_name varchar(50) NOT NULL,
mgrid int,
processed tinyint default 0)
-- initialize @Reports with direct reports
-- of the given employee
INSERT @reports
SELECT emp_id, emp_name, emp_mgr, 0
FROM employees
WHERE emp_id = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose
direct reports are going to be found
in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @reports
SELECT e.emp_id, e.emp_name, e.emp_mgr, 0
FROM employees e, @reports r
WHERE e.emp_mgr=r.emp_id
and e.emp_mgr <> e.emp_id
and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records
whose direct reports have
been found in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the
-- function the required columns
INSERT @retFindReports
SELECT emp_id, emp_name, mgrid
FROM @reports
RETURN
END
You are now good to go :
In your SQL Query Analyzer, run the following query :
select * from fn_FindReports(3).
Substitude 3 with whatever Employee No you want to test.
Labels: sql
