Recursive / hierarchical queries in SQL Server

Hello friends J

We all know how difficult it is handling recursive functions in any programming language; it is even more difficult to write recursive queries in SQL Server earlier versions like SQL 7.0 or 2000.

SQL Server 2005 has introduced a new feature called common table expression, which made our job so easy when working with hierarchical data like organization levels in a company or file storage system or component based storage etc.

Even though it was introduced with SQL Server 2005, now only I got the chance to look at it and found interesting. I hope you find this article useful J

WITH Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

Syntax

[ WITH <common_table_expression> [ ,...n ] ]   <common_table_expression>::=         expression_name [ ( column_name [ ,...n ] ) ]     AS         ( CTE_query_definition )

Arguments

expression_name

Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause

column_name

Specifies a column name in the common table expression

CTE_query_definition

Specifies a SELECT statement whose result set populates the common table expression.

Example:-Click here to download the script

CREATE TABLE Dept( Dept_id INT PRIMARY KEY, DeptName VARCHAR(100), Loc VARCHAR(100))

GO

 

INSERT INTO Dept VALUES(101,‘Admin’,‘Chennai’)

INSERT INTO Dept VALUES(102,‘Sales’,‘Hyderabad’)

INSERT INTO Dept VALUES(103,‘Operations’,‘Delhi’)

INSERT INTO Dept VALUES(104,‘IT’,‘Banglore’)

 

GO

CREATE TABLE Employee(Emp_id INT PRIMARY KEY, EmpName VARCHAR(100),DOJ DATETIME, Salary MONEY, Dept_id INT REFERENCES Dept(Dept_id))

 

GO

INSERT INTO Employee VALUES (101,‘Shivan’,‘01-Jan-2005′,20000,101,NULL)

INSERT INTO Employee VALUES (102,‘Ram’,‘01-Dec-2006′,15000,103,101)

INSERT INTO Employee VALUES (103,‘Kokila’,‘11-Feb-2006′,15000,103,101)

INSERT INTO Employee VALUES (104,‘Jagan’,‘21-Aug-2007′,10000,104,102)

INSERT INTO Employee VALUES (105,‘Ela’,‘18-Mar-2007′,10000,104,102)

INSERT INTO Employee VALUES (106,‘Kannan’,‘16-Jun-2007′,10000,104,103)

INSERT INTO Employee VALUES (107,‘Zerold’,‘29-Nov-2007′,10000,104,103)

INSERT INTO Employee VALUES (108,‘Remya’,‘23-Apr-2008′,5000,102,104)

INSERT INTO Employee VALUES (109,‘Seetha’,‘29-Dec-2008′,5000,102,104)

INSERT INTO Employee VALUES (110,‘Ramanath’,‘14-Sep-2008′,5000,102,105)

INSERT INTO Employee VALUES (111,‘Betsy’,‘25-Oct-2008′,5000,102,105)

INSERT INTO Employee VALUES (112,‘Sanga’,‘07-Mar-2008′,5000,102,106)

INSERT INTO Employee VALUES (113,‘Abhi’,‘20-Jan-2008′,5000,102,106)

INSERT INTO Employee VALUES (114,‘Chandra’,‘21-Feb-2008′,5000,102,107)

INSERT INTO Employee VALUES (115,‘Vvs’,‘11-May-2008′,5000,102,107)

GO

 

WITH EmpParent(Emp_Id, EmpName, Manager_id, EmpLevel) AS

(

          SELECT Emp_id, EmpName, Manager_id, 1 AS EmpLevel

          FROM Employee

          WHERE Manager_id IS NULL

          UNION ALL

          SELECT e.Emp_id, e.EmpName, e.Manager_id, EmpLevel + 1

          FROM Employee e

          INNER JOIN EmpParent p

          ON e.Manager_Id = p.Emp_id

)

SELECT * FROM EmpParent ORDER BY EmpLevel,Manager_id

 

Output:

Emp_Id	EmpName	Manager_Id	EmpLevel
101	Shivan	NULL	1
102	Ram	101	2
103	Kokila	101	2
104	Jagan	102	3
105	Ela	102	3
106	Kannan	103	3
107	Zerold	103	3
108	Remya	104	4
109	Seetha	104	4
110	Ramanath	105	4
111	Betsy	105	4
112	Sanga	106	4
113	Abhi	106	4
114	Chandra	107	4
115	Vvs	107	4

 

 

GO

 

WITH EmpParent AS

(

          SELECT Emp_id, EmpName, Manager_id, 1 AS EmpLevel, (CONVERT(VARCHAR(255), REPLICATE(‘****’, 1)) + EmpName) AS EmpName

          FROM Employee

          WHERE Manager_id IS NULL

          UNION ALL

          SELECT e.Emp_id, e.EmpName, e.Manager_id, EmpLevel + 1, (CONVERT(VARCHAR(255), REPLICATE(‘****’, EmpLevel + 1 )) + e.EmpName) AS EmpName

          FROM Employee e

          INNER JOIN EmpParent p

          ON e.Manager_Id = p.Emp_id

)

SELECT * FROM EmpParent  ORDER BY EmpLevel,Manager_id

 

Output:

Emp_Id	Manager_Id	EmpLevel	EmpName
101	NULL	1	****Shivan
102	101	2	********Ram
103	101	2	********Kokila
104	102	3	************Jagan
105	102	3	************Ela
106	103	3	************Kannan
107	103	3	************Zerold
108	104	4	****************Remya
109	104	4	****************Seetha
110	105	4	****************Ramanath
111	105	4	****************Betsy
112	106	4	****************Sanga
113	106	4	****************Abhi
114	107	4	****************Chandra
115	107	4	****************Vvs

 

Source :- MSDN J J J