SQL SERVER – Simple Example of Recursive CTE

Table name is WorkFlow :

 In the above table every workflow step contains its next step Id .So i want to get the record in which sequence starts from initial state and iterates to final step. For this Recursive CTE is the best solution in SQL SERVER

Query tro fetch the sequence is


WITH Emp_CTE AS (
SELECT WorkFlowId ,Name ,NextSteps, 0 As Level1
FROM WorkFlow
WHERE NextSteps IS NULL


UNION ALL
SELECT e.WorkFlowId ,e.Name ,e.NextSteps,ecte.Level1 + 1
FROM WorkFlow e
INNER JOIN Emp_CTE ecte ON ecte.WorkFlowId = e.NextSteps
)
SELECT *
FROM Emp_CTE
order by level1 desc
GO


Result set given below