T-SQL Tuesday #18–CTE A Simpler Form Of Recursion

SQL Server Best Practices
SQL Server Best Practices
It’s T-SQL Tuesday time folks. Bob Pusateri (blog|twitter) is the host and has chosen Common Table Expressions (CTEs) as the topic of the month.

Several ideas came to mind for writing about CTEs, one of the best uses I’ve seen for one recently was to grab the name of the most recent backup file for a database. You’ll have to ask Aaron Nelson (blog|twitter) to hook you up with that one though.

I thought I’d write about an interesting problem that was posed to me in an interview a couple of months ago.

Here’s a table

I was given a table with two columns; ManagerID, EmployeeID.

This table was populated with a few values thusly:


create table #ManagersEmployees (ManagerID int, EmployeeID int)

insert into #ManagersEmployees 
values(1,2), (2,3), (2,4), (2,5), (3,6), (3,7), (3,8)
    , (4,10),(5,11),(5,12), (12,13), (12,14)

I was asked to write a recursive procedure to pull out the manager, employee tree for a given ManagerID.

CTEs to the rescue

Having done a little work with CTEs and understanding that I could easily write a recursive query using them I was able to quite quickly put together a script to pull the information needed. By throwing it into a procedure it could quickly and easily be executed.

CREATE PROCEDURE ManagerRecursion_CTE @ManagerID INT


;WITH Managers_CTE (ManagerID, EmployeeID )
AS ( SELECT ManagerID, EmployeeID FROM #ManagersEmployees  
        WHERE ManagerID = @ManagerID
    SELECT e.ManagerID, e.EmployeeID 
        FROM #ManagersEmployees e 
            INNER JOIN Managers_CTE c on e.ManagerID = c.EmployeeID)
SELECT * FROM Managers_CTE ORDER BY ManagerID, EmployeeID

I tested and this worked nicely, it was a simple solution and provided the requested results.

That’s not recursion

The trouble is that while the results were not correct I was advised that this was not recursive and did not meet the criteria. Back to the drawing board then.

After a lot more work I came up with the following:

Continue reading on SirSQL.net.

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *