In this post, i will explain you how to implement 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)
WITH RETURNS NULL ON NULL INPUT -- Returns NULL on NULL Value
IF(@n = 0)
RETURN @n * dbo.CalculateFactorial (@n - 1)
SELECT dbo.CalculateFactorial(5) AS Factorial;
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
CASE WHEN @Number < 0 THEN NULL ELSE 1 –- Checking NULL or Negative value
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.
And here is the 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.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.