Recursive Common Table Expression

Common Table Expression (CTE) as you know is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. If you are new to CTE's, read the BOL over here.

In this sample, we will see how to use Recursive CTE's to find out the Grade/Band of an employee in a company.

Case Scenario : We have a table called Employees. We need to display the Grade/Band of each Employee in the company. The Grade will be determined by the hierarchy of Managers above the person.For the sake of demonstrating CTE's, we will create the following columns in the Employees table - ID, EName, Designation and ManagerID. Using recursive CTE, we will then display the Grade of each employee in the company. Grade 1 is the highest grade, given only to those who do not have managers above them.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine. Follow the link to know the details…http://www.sqllion.com/2010/08/common-table-expressions-cte/