Definition:

Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition.

When we have the set of code and we want use it multiple time we can create recursive store procedure. This recursive procedure helps us to use the same set of code n number of times.

Example:

Factorial number

A factorial is the multiple of any number by all the lesser numbers down to two.

For example, factorial(10) is equal to 10*9*8*7*6*5*4*3*2.

CREATE PROCEDURE [dbo].[Factorial_ap]

(
    @Number Int,
    @RetVal Int OUTPUT

)

AS
    DECLARE @In Int
    DECLARE @Out Int
    IF @Number != 1
        BEGIN
        SELECT @In = @Number – 1
        EXEC Factorial_ap @In, @Out OUTPUT
        SELECT @RetVal = @Number * @Out
    END
        ELSE
            BEGIN
                SELECT @RetVal = 1
            END

RETURN

GO

Here, Procedure call itself until condition become false.