Arithmetic overflow error converting nvarchar to data type numeric

Question

I have a stored procedure which works fine in my local environment as well as the QA environment.

However at the UAT environment at the client it gives the error

System.Data.SqlClient.SqlException:
Message number="8115" severity="16" state="8">Arithmetic overflow error converting nvarchar to data type numeric.

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

(
        @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'
                                        )) <= @TotalHHInternalTo
            )   
        )
     )

The variable @TotalHHInternalTo 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 @TotalHHInternalTo I would like to display it in the search result.

I added the isNumeric condition to check if the value picked up from the xml is a numeric value.

However I still get an overflow error.

RichardTheKiwi · Accepted Answer · 2012-10-29 12:07:00Z

1

Check my brackets, but you need to convert it to explicit short-circuit boolean evaluation using CASE. SQL Server is free to evaluate AND conditions in either order, and you got the wrong end of the stick.

        CASE WHEN IsNumeric(E.[Xml].value(
                                'declare default element namespace "http://www.focus-solutions.co.uk/focus360/1.0";
                                (/Event/Data/CustomData/DataXML/ProductData/ProductParty/ProductCategory[@code != ''Protection'']/Product/FundValue)[1]',
                                'nvarchar(50)'
                                ) ) = 0
        THEN 0
        WHEN
        EXISTS
        (
            SELECT 
                1
            FROM E.[Xml].nodes(
                'declare default element namespace "http://www.focus-solutions.co.uk/focus360/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.focus-solutions.co.uk/focus360/1.0";
                                    (FundValue)[1]',
                                    'decimal'
                                    )) <= @TotalHHInternalTo
        )
        THEN 1
        ELSE 0 END = 1

answered Oct 29, 2012 at 12:07

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

rajn70 Over a year ago

I made the changes as suggested by Richard, it still gives the same error. <Message number="8115" severity="16" state="8">Arithmetic overflow error converting nvarchar to data type numeric.</Message>

RichardTheKiwi Over a year ago

Can you post a fragment of your XML, at least a small part that would cause this error? It shouldn't be too hard to divide and conquer.

rajn70 Over a year ago

This is the xml from where i am picking up the FundValue:

rajn70 Over a year ago

RichardTheKiwi Over a year ago

That's not it. Using your original condition doesn't break it here SQL Fiddle

|

Collectives™ on Stack Overflow

Arithmetic overflow error converting nvarchar to data type numeric

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related