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:
USE TempDB GO 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) GO
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 AS SET NOCOUNT ON ;WITH Managers_CTE (ManagerID, EmployeeID ) AS ( SELECT ManagerID, EmployeeID FROM #ManagersEmployees WHERE ManagerID = @ManagerID UNION ALL 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 GO
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.