What is recursive stored procedure?
SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.
CREATE PROCEDURE [dbo].[Fact]
@RetVal Integer OUTPUT
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
SELECT @RetVal = 1