What 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?
Answers (1)Add Answer
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.
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.