CODEWITHSUNDEEP
sqlsql-server
Khalid
Khalid

Reputation: 367

After Success Execution i got error :Arithmetic overflow error converting expression to data type int

I am reading an sql article on how to cleverly use sql queries ,i get the bellow article which is calculating factorial and its working for number 5 but when i change to another number bigger than 12 it push the error:Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int. I think about 12 is the sum of numbers<=5 which are 5 4 3 2 the bellow query is working for all num<12 but when i change num to bigger than 12 it does not work even if i change the condition "num<"

 with fact as (
        select 1 as fac, 1 as num
        union all
        select fac*(num+1), num+1
        from fact
        where num<12)
    select fac
    from fact
    where num=5

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your code is a little strange -- counting up and then using the outer where to stop.

I would just put in one number and count down. If you use decimal(38, 0), you can go much higher:

with fact as (
        select cast(1 as decimal(38, 0)) as fac, 33 as num
        union all
        select fac * num, num - 1
        from fact
        where num > 0
       )
select max(fac)
from fact;

This returns 8,683,317,618,811,886,495,518,194,401,280,000,000. That seems sufficiently large for most practical application.

Upvotes: 3

SE1986
SE1986

Reputation: 2760

The result of the calculation when using the number 13 is greater than the range of values an integer can hold

Instead you will have to convert to BIGINT

with fact as (
                select   CONVERT(BIGINT,1) as fac,
                         1  as num
                union all
                select   CONVERT(BIGINT,fac*(num+1)),
                         num+1
                from     fact
                where    num<13
             )
select fac
from fact
where num=13

Upvotes: 1

Related Questions