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:
  1. CREATE FUNCTION [dbo].[CalculateFactorial] (@n int = 1)
  2. RETURNS INT
  3. WITH RETURNS NULL ON NULL INPUT -- Returns NULL on NULL Value
  4. AS
  5. BEGIN
  6. IF(@n = 0)
  7. BEGIN
  8. RETURN 1;
  9. END
  11. RETURN @n * dbo.CalculateFactorial (@n - 1)
  12. END;
Call Function
  1. 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:
  1. DECLARE @Number INT, @Fact INT;
  2. SET @Fact = 1;
  3. SET @Number = 5; -- To Find Factorial of number
  5. WITH Factorial AS -- Defined Common Table Expression
  6. (
  7. SELECT
  8. CASE WHEN @Number < 0 THEN NULL ELSE 1 –- Checking NULL or Negative value
  9. END N
  10. UNION all
  11. SELECT (N+1)
  12. FROM Factorial
  13. WHERE N < @Number
  14. )
  15. SELECT @Fact = @Fact * N from Factorial –- Multiplying temp results
  17. 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!!