Recursive function in SQL 2008 to calculate factorial of a number

Question

I was asked in an interview to create a function to calculate factorial of a number, that calls itself recursively.

My attempt to do this resulted in an error message:

"Msg 217, Level 16, State 1, Line 1 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the query:

CREATE FUNCTION fnGetFactorial ( @num INT )
RETURNS INT
AS
    BEGIN
        DECLARE @result INT
          , @n INT

        WHILE ( @num <> 1 )
            BEGIN
                SELECT  @n = dbo.fnGetFactorial(@num)

                SELECT  @result = @num * @n

                SET @num = @num - 1
            END

        RETURN @result
    END

So, is it possible do it in a similar manner to my code above, with minimal modification? I don't want to use a CTE.

I don't believe there is a way around the max nesting level. Why do you not want to use a CTE? — JBond
– JBond, Commented Sep 18, 2015 at 12:46
I think the logic of the proc is flawed - if you pass in, say 4, the first thing it's going to do is recursively call itself, passing in 4.... then that func is first going to recursively call itself passing in 4.. and so on. That's why you're hitting the nesting level. If you have another think about the logic of the function so that it recursively calls itself correctly (i.e. if you pass in 4, you should only be aiming to nest 4 levels deep at most - 3 really, seeing as you don't really need to multiplt by x1), then you can end up with a function that'll calc the factorial up to 32! — PulseLab
– PulseLab, Commented Sep 18, 2015 at 12:53
You have an infinite loop. It never reaches SET @num = @num - 1 when the number is not 1. — JodyT
– JodyT, Commented Sep 18, 2015 at 12:53

George T · Accepted Answer · 2015-09-18 13:02:47Z

3

ALTER FUNCTION dbo.fnGetFactorial (@num int)
RETURNS INT
AS
BEGIN
    DECLARE @n  int

    IF @num <= 1 SET @n = 1
    ELSE SET @n = @num * dbo.fnGetFactorial(@num - 1)

    RETURN @n
END
GO

answered Sep 18, 2015 at 13:02

George T

8578 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JBond Over a year ago

Note that the data type should be a FLOAT. As an INT only allows factorials up to 12. BIGINT to 20 and FLOAT to 32 as it hits the recursion limit.

varun kumar dutta Over a year ago

Thanks @George Tanasie, its working fine. I appreciate you for your time spent here for my query.

Collectives™ on Stack Overflow

Recursive function in SQL 2008 to calculate factorial of a number

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related