Wednesday, February 17, 2010

Recursion in SQL

Stored Procedure for calculating the Recursion

CREATE PROC sp_Factorial
@InPara int,
@OutPara int OUTPUT
AS
DECLARE @InCalc int
DECLARE @OutCalc int
IF @InPara != 1
BEGIN
SELECT @InCalc = @InPara - 1
EXEC sp_Factorial @InCalc, @OutCalc OUTPUT
SELECT @OutPara = @InPara * @OutCalc
END
ELSE
BEGIN
SELECT @OutPara = 1
END
RETURN GO

Following is the code to execute the SP "sp_Factorial"

DECLARE @CalcOut int
DECLARE @CalcIn int
SELECT @CalcIn = 6
EXEC sp_Factorial @CalcIn, @CalcOut OUTPUT
PRINT ' factorial of ' + CAST(@CalcIn AS varchar) + 'is ' + CAST(@CalcOut AS varchar)
GO