What Is Recursive Stored Procedure In SQL Server And How To Use It

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.]

Example

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

Output

Thank you.