Want to create a stored procedure with CTE and want to know what the performance hit will be:
DECLARE @Directory Table( DirId INT, ParentId INT ) DECLARE @Files Table( FileId INT, DirId INT ) INSERT INTO @Directory SELECT 1, NULL INSERT INTO @Directory SELECT 2, 1 INSERT INTO @Directory SELECT 3, 1 INSERT INTO @Directory SELECT 4, 2 INSERT INTO @Files SELECT 1, 1 INSERT INTO @Files SELECT 2, 1 INSERT INTO @Files SELECT 3, 2 INSERT INTO @Files SELECT 4, 2 INSERT INTO @Files SELECT 5, 3 INSERT INTO @Files SELECT 6, 3 INSERT INTO @Files SELECT 7, 4 INSERT INTO @Files SELECT 8, 4 ;WITH Directories AS ( SELECT DirId, ParentID FROM @Directory WHERE DirId = 2 UNION ALL SELECT d.DirId, d.ParentID FROM @Directory d INNER JOIN Directories p ON d.ParentId = p.DirId ) SELECT * FROM Directories d INNER JOIN @Files f ON d.DirId = f.DirId