factorial function error - Maximum stored procedure, function, trigger, or view nesting level exceeded

Question

I'm trying to run the following factorial function but I'm getting the following error.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Is this possible to fix?

The binomial formula given that
x = total number of “successes” (pass or fail, heads or tails etc.) -- 0.025
P = probability of a success on an individual trial --Rate  0.000729
n = number of trials   -- Denominator 13725
q= 1 – p –- is as below:

---

    DECLARE @n float(53), @x float(53), @p decimal(10, 10), @q decimal(10,10)
    DECLARE @p0 decimal(10,10), @p1 decimal(10,10), @p2 decimal(10,10), @n1 float(53), @n2 float(53), @n3 float(53)
    SELECT @n =13725 , @x = 0.025 , @p = 0.000729,@q=1-@p 
    SELECT @x = 0.025
    SELECT @n1 = dbo.factorial(@n) 
    SELECT @n2 = dbo.factorial(@n-@x)
    SELECT @n3 = 1
    SELECT @p1 = ( @n1/(@n2 * @n3))*power(@p, @x)*power(@q,@n-@x)
    select @p1 as 'Probability of 0 people getting lung illness'

---factorial function
    create function dbo.factorial(@num1 float(53))
    returns float(53)
    as
    begin
    declare @factno bigint;
     
    if  (@num1=1)  set @factno=1;
    else 
     
    set @factno= @num1*dbo.factorial(@num1-1);
     return @factno;

    end
    go

---

Answer 1

Recursive computations aren't where SQL Server (nor any other database) shines. The best solution is to move this out of the database and calculate it in your application. But if you absolutely must do this in the database, use an iterative method instead of recursive one, for example:

create function dbo.factorial(@num1 float(53))
returns float(53)
as
begin
    declare @factno bigint;

    ;With Nums As
    (
        select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
        FROM sys.objects
    )
    SELECT @factno = POWER(10.0, SUM(LOG10(RN)))
    FROM Nums
    WHERE RN <= @num1

    return @factno;
end