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.

Example:-

CREATE PROCEDURE [dbo].[Fact]

(

@Number Integer,

@RetVal Integer OUTPUT

)

AS

DECLARE @In Integer

DECLARE @Out Integer

IF @Number != 1

BEGIN

SELECT @In = @Number – 1

EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)

SELECT @RetVal = @Number * @Out

END

ELSE

BEGIN

SELECT @RetVal = 1

END

RETURN

GO