In this article, we will learn about the concept of recursive stored procedures.
Recursive stored procedure refers to a stored procedure that calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code N number of times.
[NOTE: A stored procedure can call itself up to the maximum nesting level of 32.]
Here, we will take an example of calculating the factorial of N number.
A factorial is the multiple of any number by all the lesser numbers down to two. For example, factorial(5) is equal to 5 * 4 * 3 * 2*1.
CREATE PROCEDURE USP_FactorialCalculate @Number Integer, @RetVal Integer OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @In Integer DECLARE @Out Integer IF @Number != 1 BEGIN SELECT @In = @Number - 1 EXEC USP_FactorialCalculate @In, @Out OUTPUT SELECT @RetVal = @Number * @Out END ELSE BEGIN SELECT @RetVal = 1 END END;
Now, Execute the above-stored procedure as mentioned below,
DECLARE @Result INTEGER = 0 EXEC USP_FactorialCalculate 4, @Result OUTPUT SELECT @Result Factorial