How to create a Recursive CTE

Problem: How to create a recursive result set.

Solution: WITH common_table_expression (Transact-SQL).

Common table Expression(CTE) specifies a temporary user named result set. It can be derived from a simple select statement query result set. CTE leverages to reference itself and creates a recursive CTE.

 A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A recursive CTE consists of three elements:

1. Invocation of the routine.
2. Recursive invocation of the routine.
3. Termination check.

NOTE:

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. When testing the results of a recursive query, you can limit the number of recursion levels allowed for a specific statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. For more information, see Query Hints (Transact-SQL) and WITH common_table_expression (Transact-SQL).

Example:

In this article I am going to show you a simple example on how we can create and use recursive CTE’s

Example1:

WITH CTE AS

(

--Step1: invocation

SELECT 1 as i, 'Initial' as source

UNION ALL

--Step 2: Recursive Invocation of the routine

SELECT i + 1, 'CTE    ' as Source-- giving spaces on the source to match the type definition of the initial.

FROM CTE

--Termination Check

WHERE i + 1 <= 10--limiting the recursion

)

SELECT i,Source FROM CTE

Now as you can see the comments on the above code I have initialized the CTE with a 1 as I and made a reference to its own and increment the column by 1 later limiting the number of iterations by saying not greater than 10.

OUTPUT:

Example 2:

In this example I am going to show you a real life scenario where we have employees table and have managers assigned to them.

--------------------------------------------

-- Initiation

--------------------------------------------

;WITH Employee (ID, Name, MgrID) AS

(

    SELECT 1,      'Adam',      NULL   UNION ALL

    SELECT 2,      'Scott',       1      UNION ALL

    SELECT 3,      'Jesse',      1      UNION ALL

    SELECT 4,      'Trevin',       2      UNION ALL

    SELECT 5,      'Coleman',     NULL   UNION ALL

    SELECT 6,      'Julio',     5      UNION ALL

    SELECT 7,      'James',       5      UNION ALL

    SELECT 8,      'Dev',        6  

)

--------------------------------------------

-- Recursive CTE - Chained to the above CTE

--------------------------------------------

,CTE AS

(

    --  Anchor

    SELECT   ID

            ,Name

            ,MgrID

            ,nLevel = 1

            ,Manager =Name

    FROM Employee

    WHERE MgrID IS NULL

    UNION ALL

    --  Recursive query

    SELECT   E.ID

            ,E.Name

            ,E.MgrID

            ,H.nLevel+1

            ,Manager

    FROM Employee   E

--  Termination Check

    JOIN CTE  H ON E.MgrID = H.ID

)

SELECT *

FROM CTE

ORDER BY Manager, nLevel

OUTPUT: