How to retrieve data recursively

There are many times where we need to retrieve hierarchy data in sql. Like given a manager displaying all the employees below him for many levels. Or given a product, to find all the sub-parts and sub-parts for the Sub-parts. We need a recursive function to display the data. In Sql server 2005 we can acheive this using Common Table Expression. For understanding this check the following example.

Create the employee table with manager id and insert few records.


CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)


INSERT INTO Employees VALUES(1 , NULL, 'Nancy' )
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' )
INSERT INTO Employees VALUES(3 , 1 , 'Janet' )
INSERT INTO Employees VALUES(4 , 1 , 'Margaret')
INSERT INTO Employees VALUES(5 , 2 , 'Steven' )
INSERT INTO Employees VALUES(6 , 2 , 'Michael')
INSERT INTO Employees VALUES(7 , 3 , 'Robert' )
INSERT INTO Employees VALUES(8 , 3 , 'Laura' )
INSERT INTO Employees VALUES(9 , 3 , 'Ann' )
INSERT INTO Employees VALUES(10, 4 , 'Ina' )
INSERT INTO Employees VALUES(11, 7 , 'David')
INSERT INTO Employees VALUES(12, 7 , 'Ron' )
INSERT INTO Employees VALUES(13, 7 , 'Dan' )
INSERT INTO Employees VALUES(14, 11 , 'James' )
GO

/*The following displays all the employees who are under that manager till the last level of employee. */

Declare @empid int
set @empid = 3 /*Any manager's employee id*/

WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid /* Managers employee Id */
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
SELECT * FROM Employees_Subtree
GO

If we need to get the manager Name just add a self join to employees table.