SQL Server Hosting – How to implement recursion in SQL Server?

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:

Call Function

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:

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.

Happy Coding!!

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.