SQL Proc factorial

Proc factorial

use pubs
go

SET NOCOUNT ON
go

if exists (select * from sysobjects where name='factorial' and type='P')
	DROP PROC factorial
go


-- SQL Server supports recursion !!
-- Recursively calculate factorial of numbers between 0 and 12
-- Parameters greater than 12 are disallowed as result overflows the bounds of an int

CREATE PROC factorial @param1 int
as
declare @one_less int, @answer int
IF (@param1 < 0 OR @param1 > 12)
	BEGIN
		-- Illegal Parameter Value .  Must be between 0 and 12
		RETURN -1
	END


IF (@param1 = 0 or @param1 = 1)
	select @answer=1
else
	BEGIN
	SELECT @one_less=@param1 - 1
	exec @answer=factorial @one_less   -- RECURSIVELY CALL ITSELF
	IF (@answer= -1)
		BEGIN
			RETURN -1
		END

	SELECT @answer=@answer * @param1
		IF (@@error <> 0)
			RETURN -1
	END

RETURN(@answer)
go

declare @answer numeric,@param int
select @param=0
WHILE (@param <= 12)
BEGIN
	exec @answer=factorial @param
	IF (@answer = -1)
		BEGIN
			RAISERROR('Error executing factorial procedure.',16,-1)
			RETURN
		END
	select convert(varchar,@param) + '! = ' + convert(varchar,@answer)
	select @param=@param + 1
END




-- Alternative solution uses an output parameter to return a value of 
-- type decimal (38,0)

if exists (select * from sysobjects where name='factorial2' and type='P')
	DROP PROC factorial
go

CREATE PROC factorial @param1 decimal(38,0), @answer decimal(38,0) output
AS
DECLARE @one_less decimal(38,0), @status int

IF (@param1 < 0 OR @param1 > 
32)
    BEGIN
        -- Illegal parameter value. Must be between 0 and 12.
        RETURN -1
    END

IF (@param1=0 or @param1=1)
    SET @answer=1
ELSE
    BEGIN
    SET @one_less=@param1 - 1
    EXEC @status=factorial @one_less, @answer output 
    -- Recursively call itself
    IF (@status= -1)
        BEGIN
            RETURN -1
        END

    SET @answer=@answer * @param1

        IF (@@ERROR <> 0)
            RETURN -1
    END

RETURN  0


-- Alternative iterative solution does not have the restriction of 32
-- nesting levels.

if exists (select * from sysobjects where name='factorial2' and type='P')
	DROP PROC factorial2
go


CREATE PROC factorial2 @param1 int, @answer numeric(38,0) output
as
declare @counter int
IF (@param1 < 0 OR @param1 > 34)
	BEGIN
	RAISERROR ('Illegal Parameter Value .  Must be between 0 and 34',16,-1)
	RETURN -1
	END

SELECT @counter= 1, @answer=1


WHILE (@counter < @param1 AND @param1 <> 0 ) BEGIN
	SET @answer=@answer * (@counter + 1)
        SET @counter=@counter+1
      END
	
RETURN
go

declare @answer numeric(38,0),@param int
select @param=0
WHILE (@param <= 34)
BEGIN
	exec factorial2 @param, @answer output
	select convert(varchar(50),@param) + '! = ' + convert(varchar(50),@answer)
	select @param=@param + 1
END