SQL: How to get depth from a recursive cte

If you need to know what level deep you are when recursing through a series of parent child relationships, there is a pretty easy way.

DECLARE @Folders TABLE ( ID INT NOT NULL, FolderTypeLID INT NOT NULL, Depth INT NOT NULL,SortOrder INT NOT NULL );
WITH cteFolder( FolderID,Depth )
AS
(
SELECT @topFolderID,Depth = 0
UNION ALL
SELECT f.ID, Depth +1
FROM Folder f
JOIN cteFolder cte
ON f.ParentID = cte.FolderID
)

 

Happy Coding!

SQL: How to include an incrementing column in your query without a cursor or loop

I had the need to do a recursive call that included depth and sort orders. I wanted to reformat the sort orders in a single query without using a cursor or loop.

Turns out there is an easy way with SQL 2005 or higher.

@Folders is a table var from my CTE with Depth and SortOrder in it.

SELECT ID,(ROW_NUMBER() OVER (ORDER BY f.Depth,f.SortOrder,r.SortOrder)) * 100 as SortOrder
FROM someTable r
JOIN @Folders f
ON r.FolderID = f.ID
ORDER BY SortOrder;

Happy Coding!