Recursive query using CTE

Recursion in many programming language refers to a scenario when a function calls itself until over and over until its exit condition is met. For example the c# function blow will call itself until the number that is passed to the function equals to one.

static int factorial(int number)
{
    if (number == 1)
        return 1;
    return number * factorial (number - 1);
}

It is possible to use user defined function in SQL something that works exactly like other programming languages but there is a recursion limit. For example a maximum of 32 recursions are allowed for user defined functions in SQL Server. So if we can’t use user defined functions to implement recursion what is the best alternative to create recursive query then?

According to Microsoft’s online book a query is referred to as a recursive query when it references a recursive CTE. Recursive queries are good to display data that has tree like structure like employees in the organization chart, parent child relationship, and even to do many tasks such as splitting a comma separated string to separate elements.

Recursive query has one or more anchor members which can be join using UNION and only one recursive member which will be joined with anchor members using UNION ALL.

Whenever we write recursive queries it is important to think of the three elements invocation of the routine; recursive invocation of the routine and termination check. That is the initialization, recursion using the CTE name itself and criteria to exit out of the recursion.
Let us see how recursive queries work in action:
If we want to represent the above C# factorial example with SQL

DECLARE @NUMBER  INT = 5;
WITH FACTORIAL(VALUE) AS(
    -- Ancher member --
    SELECT @NUMBER VALUE
    UNION ALL	
    -- Recursive member --
    SELECT VALUE - 1
    FROM LIST
    WHERE VALUE &gt; 1
)
SELECT VALUE 
FROM FACTORIAL

The above query will give us a row numbers from 5 to 1 and if we want to get just to final result of the factorial we could change the external query using

SELECT Sum(value) Result
FROM LIST

By default the maximum recursion allowed for CTE query is 100 recursions and if we try to go above that it will give us an error similar to the bottom one.

We could use an Option(maxrecursion) to change the default value

DECLARE @NUMBER  INT = 200;
WITH LIST(VALUE) AS(
    -- Ancher member --
    SELECT @NUMBER VALUE
    UNION ALL	
    -- Recursive member --
    SELECT VALUE - 1
    FROM LIST
    WHERE VALUE &gt; 1
)
SELECT SUM(VALUE) RESULT
FROM LIST
OPTION(maxrecursion 200) -- change max recursions from the default 100 to 200
				 --use 0 for maxrecursion to recuse with no limit