Searching Hierarchical data in database : Part 1

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.