1

I have the following statement that is executed at the end of a really large stored procedure.

UPDATE myTable
SET    DiffPerc = CAST(CASE
                         WHEN ( CASE
                                  WHEN SimExt = 0
                                       AND StdExt = 0 THEN 0
                                  WHEN StdExt = 0 THEN 99999
                                  ELSE SimExt - StdExt / StdExt
                                END ) * 100 > 99999 THEN 99999
                         ELSE ( CASE
                                  WHEN SimExt = 0
                                       AND StdExt = 0 THEN 0
                                  WHEN StdExt = 0 THEN 99999
                                  ELSE SimExt - StdExt / StdExt
                                END ) * 100
                       END AS DECIMAL(8, 2))

The idea is that I have a fields that determines the percentage difference of one value over another. The field DiffPerc is percentage difference of SimExt to StdExt. This routine has worked every day, for well over a year, however, starting two days ago I started to getting the following error message:

Arithmetic overflow error converting numeric to data type numeric.

I understand what this message means, but the whole point of the embedded case statement is to to both test for 0's in the denominator, as well as check for any grossly high value percentages before the value is packed into the DECIMAL (8, 2) field.

What am I missing? How can I update this statement to account for all possible edge cases and handle any overflow before it happens?

Also, please note that the hard-coded 99999 value as a percent is a a flag to the end users that someone has screwed something up.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
RLH
  • 15,230
  • 22
  • 98
  • 182
  • 3
    Is `100 * (SimExt - StdExt / StdExt)` ever `< - 999999`? – Martin Smith Aug 17 '12 at 14:09
  • 1
    Martin Smith: ...and the ability of the user to do really dumb things has, once again, astounded me. This should have never happened (won't go into the details of what this process is for) but, yes, this appears to be the problem. This data comes from another system. I'm not even sure how the user managed to get such a value in the system. – RLH Aug 17 '12 at 15:01

4 Answers4

1

Maybe it is a typo, but it seems to me that you are missing a set of parethesis.

I.e.

(SimExt - StdExt) / StdExt else you will always do SimExt - 1 since the subtraction is done after the dividing.

Note the difference:

select CAST((99999999 - 99999998) / 99999998 as decimal(8,2))
select CAST(99999999 - 99999998 / 99999998 as decimal(8,2))
Johan
  • 1,152
  • 7
  • 16
  • Well, the problem fixed itself because the data was updated in the master system. However, you are correct. This is/was an obvious mistake that I just over looked. I'm giving you the answer mark to close the question. Thank's for pointing this out. – RLH Aug 20 '12 at 16:48
0

The end result exceeds the size (8,2). You need to increase the scale

Madhivanan
  • 13,470
  • 1
  • 24
  • 29
  • But that is the point of the case statement testing for a value larger than 99999. Shouldn't that take whatever over-value there is and set it to 99999, before the converstion to decimal(8, 2)? That's what I want to happen. – RLH Aug 17 '12 at 14:03
0

It's probably an out of range high value. Try altering to

  (CASE  
   WHEN SimExt = 0 AND StdExt = 0 THEN 0  
   WHEN StdExt = 0 THEN 99999  
   when simext>1000000 then null
   ELSE SimExt - StdExt / StdExt  
   END) * 100 > 99999
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • That might not work. SimExt can be very large, however, if that were the case, StdExt would be large as well. These percentages that I am calculating shouldn't be any larger than 200 to 300%. – RLH Aug 17 '12 at 14:09
0

SQL does not do short circuit evaluation, so you have to protect it for that. http://en.wikipedia.org/wiki/Short-circuit_evaluation

Practically it will evaluate both cases and will chose at the end the right path.

Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • 1
    It does short circuit in a `CASE` statement in SQL Server [(mostly)](http://dba.stackexchange.com/q/12941/3690) – Martin Smith Aug 17 '12 at 14:11
  • http://stackoverflow.com/questions/8760581/sql-server-an-invalid-floating-point-operation-occurred – Dumitrescu Bogdan Aug 17 '12 at 14:14
  • 1
    That uses an aggregate expression. There are some exceptions in the link I gave earlier but `CASE` is documented to short circuit with some caveats. See BOL ["You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions."](http://msdn.microsoft.com/en-us/library/ms181765.aspx) – Martin Smith Aug 17 '12 at 14:15