1

I have a query having code like this.

set @CurrentPatchDeployPercentage = convert(numeric(5,2), (isnull(@LastMonthDeployCount, 0)* 100.00 / isnull(nullif(@TotalMachines, 0), 1)))

But when I run I am getting below error, please help.

Arithmetic overflow error converting numeric to data type numeric

Alex K.
  • 171,639
  • 30
  • 264
  • 288
vissubabu
  • 493
  • 4
  • 11
  • 25
  • numeric(5,2) allows 5 total digits, 2 fractional meaning 5 - 2 = 3 total digits on the left. A result of your calculation exceeds 999.99 and the error is raised. Increase the size of the numeric() – Alex K. Feb 07 '17 at 12:14
  • Can you try increase your numeric(5,2) to numeric(x,y) where x,y are relatively higher than what you specified – PowerStar Feb 07 '17 at 12:15
  • 3
    Possible duplicate of [Why SQL Server throws Arithmetic overflow error converting int to data type numeric?](http://stackoverflow.com/questions/2059134/why-sql-server-throws-arithmetic-overflow-error-converting-int-to-data-type-nume) – Tanner Feb 07 '17 at 12:19
  • Hi guys I increased the values to numeric(6,3) but still the same error – vissubabu Feb 07 '17 at 13:05

1 Answers1

1

Are you sure that is how you want to get your percentage? Should you move your *100.00 after the division like this?

rextester: http://rextester.com/NNOEH44668

declare @LastMonthDeployCount numeric(7,2) = 10.00;
declare @TotalMachines numeric (7,2) = 100.00;

declare @CurrentPatchDeployPercentage numeric(9,2);
    set @CurrentPatchDeployPercentage = 
      convert(numeric(5,2), 
        ( isnull(@LastMonthDeployCount, 0)
          / 
          isnull(nullif(@TotalMachines, 0), 1.0)
          ) * 100.00
        );

select @CurrentPatchDeployPercentage;

Also, make sure your @CurrentPatchDeployPercentage data type can support the highest number of @LastMonthDeployCount *100.00 for when @TotalMachines = 0 and is changed to null and then changed to 1.

Decimal/Numeric Data Type - MSDN 

Precision  Storage bytes
---------  --------------
1 - 9      5
10-19      9
20-28      13
29-38      17
SqlZim
  • 37,248
  • 6
  • 41
  • 59