Recursive function in SSMS

Sometimes for a long calculation, If the problem can be broken down into smaller versions of itself. We can be able to find a way to solve one of these smaller versions and then be able to build up to a solution to the entire problem. This is the idea behind recursion; recursive algorithms break down a problem into smaller pieces of itself which you either already know the answer to, or can solve by applying the same algorithm to each piece, and then combining the results in to a result set.

For example we can go for Factorial calculation.

We know that, fact(5)= 5*4*3*2*1= 120

Step 1: Will take the given value 5,

Step 2: we have to calculate (given value(5) – 1) and multiply with first given value (5) = 5 x4=20

Step 3: After that we have to calculate (previous calculated value (4) – 1) and multiply with older    multiplication (20) = 3 x 20 = 60

Step 4: Next we have to calculate (previous calculated value (3) – 1) and multiply with older multiplication (60) = 2 x 60 = 120

Step 5: Finally we have to calculate (previous calculated value (2) – 1) and multiply with older multiplication (60) = 1 x 120 = 120. Once the value is reached 1 then we have to stop the calculation and display the total value.

So, it is a long calculation and it call the same operation repeatedly. So, when a function call itself for execution something, then we can call the function as recursive function.

Now will go for practical experience.

CREATE FUNCTION dbo.UDF_Factorial_Calculation ( @InputNumber INT )

RETURNS INT

AS

BEGIN

DECLARE @Result_set  INT

    IF @InputNumber <= 1

        SET @Result_set  = 1

    ELSE

        SET @Result_set  = @InputNumber * dbo.UDF_Factorial_Calculation( @InputNumber - 1 )

RETURN (@Result_set)

END

Now will see the Out put:

SELECT dbo.UDF_Factorial_Calculation ( 5 ) as Factorial

Output:    Factorial

                 120