title: Arithmetic overflow error converting nvarchar to data type numeric
body: <p>I have a stored procedure which works fine in my local environment as well as the QA environment. </p>
  
  <p>However at the UAT environment at the client it gives the error </p>
  
  <blockquote>
    <p><em>System.Data.SqlClient.SqlException:</em><br>
     <em>Message number="8115" severity="16" state="8">Arithmetic overflow error
     converting nvarchar to data type numeric.</em></p>
  </blockquote>
  
  <p>It also works fine in one of the local instances installed in the client location. 
  I found out the line of code which gives an error by commenting/umcommenting lines of code and zeroed it down to</p>
  
  <pre><code>(
          @TotalHHInternalTo IS NULL
          OR
          (
              IsNumeric(E.[Xml].value(
                                      'declare default element namespace "http://www.xyz/1.0";
                                      (/Event/Data/CustomData/DataXML/ProductData/ProductParty/ProductCategory[@code != ''Protection'']/Product/FundValue)[1]',
                                      'nvarchar(50)'
                                      ) ) = 1
              AND
              EXISTS
              (
                  SELECT 
                      1
                  FROM E.[Xml].nodes(
                      'declare default element namespace "http://www.xyz/1.0";
                      /Event/Data/CustomData/DataXML/ProductData/ProductParty/ProductCategory[@code != ''Protection'']/Product') as P(E)
                  HAVING SUM(P.E.value(
                                          'declare default element namespace "http://www.xyz/1.0";
                                          (FundValue)[1]',
                                          'decimal'
                                          )) &lt;= @TotalHHInternalTo
              )   
          )
       )
  </code></pre>
  
  <p>The variable <code>@TotalHHInternalTo</code> is a parameter of type decimal which is part of search criteria passed to the stored procedure as an xml. Under Product party I have 4 product category and I need to total up all Fund value except of type Protection. If the sum of these 3 types is less than  <code>@TotalHHInternalTo</code> I would like to display it in the search result.</p>
  
  <p>I added the <code>isNumeric</code> condition to check if the value picked up from the xml is a numeric value. </p>
  
  <p>However I still get an overflow error. </p>