Recursive Queries with Common Table Expressions

 

Over the years of working with databases I had to deal with recursive queries several times, first in Access, then in Oracle, then in SQL 2000. While Oracle 9i database had support for recursive queries using “CONNECT BY”, none of the other database management systems had a clean approach to execute recursive queries.

In SQL Server 2005 Microsoft added a new feature called Common Table Expressions and many people picked it up as a way to perform recursive queries.

The use case is typically for data which contains some sort of parent-child relationship where each child can be a parent as well. MSDN has an article describing how to use CTEs, please take a look.

Let’s take an Employees table which among other employee data has EmployeeId and ManagerId. Suppose we want to find all Smith’s subordinates in that table. Of course, if there are multiple people with the same last name those will get pulled as well. If Mr. Smith is the head of the company then all employees will show up at different levels, if Mr. Smith doesn’t manage anybody then only Mr. Smith will show up. To run a request like that we need:

- to create a table valued function
- define output table
- define anchor member (or first level of the recursion)
- define recursive member
- execute expression and insert data into output table

Please take a look at the code:

CREATE FUNCTION [dbo].[uft_GetEmployeeHierarchy_ByLastName] ( -- Add the parameters for the function here @LastName varchar(500) ) RETURNS @ReturnList TABLE ( -- Add the column definitions for the TABLE variable here EmployeeId int, ManagerId int, -- [Level] as int -- may also return level ) AS BEGIN -- Fill the table variable with the rows for your result set With EmployeeHierarchy ( [LastName] , EmployeeId , ManagerId , [Level] ) as ( -- Anchor member definition SELECT Distinct ee.[LastName], ee.EmployeeId, ManagerId, 1 as [Level] FROM Employees as ee WHERE ee.[LastName] like ('%'+@LastName+'%') UNION ALL -- Recursive member definition SELECT ee.[LastName], ee.TypeModalityId, ee.ParentModalityId, [Level] + 1 as [Level] FROM EmployeeHierarchy as eh inner join Employees ee on eh.TypeModalityId = ee.ParentModalityId ) -- Statement that executes CTE INSERT INTO @ReturnList(EmployeeId, ManagerId) SELECT DISTINCT EmployeeId, ManagerId FROM EmployeeHierarchy ORDER BY [Level], ManagerId, EmployeeId RETURN END

That is it. Very simple. Please let me know if you have questions!

 

Technorati Tags: CTE,Commong Table Expressions,T-SQL,Recursion