Recursion In SQL Server

Sometimes we get a requirement to implement recursion in SQL Server. Recursion means executing queries till the condition is satisfied. This blog describes two ways to implement recursion. Here we will implement factorial of number using recursion.

Using User Defined Function(UDF)

Generally UDF create custom defined functions and always return a value. Here we will define a UDF which returns factorial of a number. See the following query:

CREATE FUNCTION [dbo].[CalculateFactorial] (@n int = 1)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT -- Returns NULL on NULL Value
AS
BEGIN
IF(@n = 0)
BEGIN
RETURN 1;
END
RETURN @n * dbo.CalculateFactorial (@n - 1)
END;

Call Function

SELECT dbo.CalculateFactorial(5) AS Factorial;

Using CTE

CTE stands for Common Table Expression. It acts as a temporary result which helps to write complex queries and implement recursion. See the following query:

DECLARE @Number INT, @Fact INT;
SET @Fact = 1;
SET @Number = 5; -- To Find Factorial of number
WITH Factorial AS -- Defined Common Table Expression
(
SELECT
CASE WHEN @Number < 0 THEN NULL ELSE 1 –- Checking NULL or Negative value
END N
UNION all
SELECT (N+1)
FROM Factorial
WHERE N < @Number
)
SELECT @Fact = @Fact * N from Factorial –- Multiplying temp results
SELECT @Fact as 'Factorial'; -- Fetch factorial value

Here we declare two variables @Fact and @Number. Then it uses CTE which selects all the numbers(starting from 1) that are less than @Number. Once we have temp results it multiplies and stored results in @Fact variable. Seethe following figure1 after executing CTE and UDF.

Figure 1: Output of Factorial of a Number

In this blog we discussed two ways to implement recursion. As per the requirement of the project you can use one of them. Performance basis try to use recursion using CTE. Because in CTE result is getting stored in temp memory but in UDF it is calling the function again and again. So UDF will take time as compared to CTE.

Happy Coding!!