## What is a Recursive Stored Procedure?

Forums SQLWhat is a Recursive Stored Procedure?

What is a Recursive Stored Procedure?  In which scenario we can use it?

can anyone explain with the help of a small example?

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.

## Subscribe

Select Categories