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, Sep 18 '15 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, Sep 18 '15 at 12:53
You have an infinite loop. It never reaches `SET @num = @num - 1` when the number is not 1. — JodyT, Sep 18 '15 at 12:53

score 3 · Answer 1 · answered Sep 18 '15 at 13:02

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 '15 at 13:02

George T

859
8
16

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. – JBond Sep 18 '15 at 13:05
Thanks @George Tanasie, its working fine. I appreciate you for your time spent here for my query. – varun kumar dutta Sep 21 '15 at 05:49

Recursive function in SQL 2008 to calculate factorial of a number

1 Answers1

Linked