Example of Recursive Query using CTE – SQL Server

I would like to give a very simple example of recursion using CTE (Common Table Expression)

As part of the demo, I will create a table with ID, EmployeeName, and ManagerID indicating org structure. Following CTE queries will fetch the records recursively downwards or upwards in the Org!

if OBJECT_ID('VijredOrginfo') IS NOT NULL
	drop table VijredOrginfo

-- Create a sample table 
create table VijredOrginfo(id int, name varchar(40), ManagerID int)

-- insert sample data 
insert into VijredOrginfo values (1, 'Raj', NULL)
insert into VijredOrginfo values (2, 'Harit', 1)
insert into VijredOrginfo values (3, 'Madhu', 1)
insert into VijredOrginfo values (4, 'Amit', 1)
insert into VijredOrginfo values (5, 'Vijay', 2)
insert into VijredOrginfo values (6, 'Srikanth', 3)
insert into VijredOrginfo values (7, 'Madhusudan', 3)
insert into VijredOrginfo values (8, 'Ram', 5)
insert into VijredOrginfo values (9, 'VijayBabu', 5)
insert into VijredOrginfo values (10, 'Raman', 5)
insert into VijredOrginfo values (11, 'Sam', 5)
insert into VijredOrginfo values (12, 'Varun', 4)
insert into VijredOrginfo values (13, 'Bob', 1)
insert into VijredOrginfo values (14, 'John', 8)

---- Select all employees
--select * from VijredOrginfo

-- CTE recursive QUERY 1
---All Employees in given employee organization
declare @i int
set @i = 5

;with CTETab as 
(
select * from VijredOrginfo 
where id = @i 
union ALL
select e.* from VijredOrginfo e
join CTETab on CTETab.id = e.ManagerID)
select distinct * from CTETab
GO

-- CTE recursive QUERY 2
--Manager chain of given employee
declare @i int
set @i = 5

;with CTETab as 
(
select * from VijredOrginfo 
where id = @i 
union ALL
select e.* from VijredOrginfo e
join CTETab on CTETab.ManagerID = e.id)
select distinct * from CTETab
Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s