Post
Ask Question

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: 
  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  
  10.       
  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  
  4.   
  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       
  16.   
  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!!