In this post I will explain how write a recursive user defined function in SQL Server
Recursive function is a function which calls itself for a certain number of times based on some business logic. We will create a simple recursive user defined function for calculating factorial of a number.
Factorial of a number n is calculated by multiplying n with (n-1) till (n-1) is greater than 1. For example, factorial of 5 is calculated as 5*4*3*2.
CREATE FUNCTION [dbo].[Factorial](@Num BIGINT) RETURNS BIGINT AS BEGIN IF @Num>0 BEGIN IF @Num >=2 SET @Num=@Num * dbo.Factorial(@Num-1) END ELSE IF @Num=0 BEGIN SET @Num=1 END ELSE SET @Num=@Num RETURN @Num END
In above recursive function we have called same function with decreasing its parameter by 1 while number is greater than or equal to 2.
In SQL Server we can call a function, stored procedure, trigger or views recursively for maximum of 32 times. But in the above Factorial function, even with a BIGINT data type we can calculate factorial of numbers till 20. After 20 it will give arithmetic overflow error