title: Getting SQL 'Arithmetic overflow error converting numeric to data type numeric.' On a statement that is tested for in-bound values
body: <p>I have the following statement that is executed at the end of a really large stored procedure.</p>
  
  <pre><code>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 &gt; 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)) 
  </code></pre>
  
  <p>The idea is that I have a fields that determines the percentage difference of one value over another.  The field <code>DiffPerc</code> is percentage difference of <code>SimExt</code> to <code>StdExt</code>.  This routine has worked every day, for well over a year, however, starting two days ago I started to getting the following error message:</p>
  
  <blockquote>
    <p>Arithmetic overflow error converting numeric to data type numeric.</p>
  </blockquote>
  
  <p>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 <code>DECIMAL (8, 2)</code> field.</p>
  
  <p>What am I missing?  How can I update this statement to account for all possible edge cases and handle any overflow before it happens?</p>
  
  <p>Also, please note that the hard-coded <code>99999</code> value as a percent is a a flag to the end users that someone has screwed something up.</p>