Arithmetic overflow error converting varchar to data type numeric ONLY in WHERE

Question

I have this query....

SELECT  
    CONVERT(NUMERIC(8, 2), ResultLevel),
    *
FROM    
    dbo.DrugTestResult
WHERE   
    DrugID = 'THC'
    AND ISNUMERIC(ResultLevel) = 1
    AND (CONVERT(NUMERIC(8, 2), ResultLevel) >= 50
         AND CONVERT(NUMERIC(8, 2), ResultLevel) <= 99999)
    AND CONVERT(DATE, AuditStamp) > '2014-1-1'

Which returns this error

Arithmetic overflow error converting varchar to data type numeric

When I comment this part of the where clause

AND ( CONVERT(NUMERIC(8, 2), ResultLevel) >= 50
      AND CONVERT(NUMERIC(8, 2), ResultLevel) <= 99999
    )

The data set is returned.

So the question is why does the CONVERT work in the SELECT, but not in the WHERE clause?

Tab Alleman · Accepted Answer · 2014-12-09 16:57:31Z

3

Because the different elements of the WHERE clause can get evaluated in any order that the query processing engine deems fit, so it can try to evaluate the CONVERT() before it evaluates the ISNUMERIC(). Therefore it can try to convert data that can't be converted.

The SELECT list, on the other hand, is evaluated after the WHERE clause, so the ISNUMERIC() has eliminated values that can't be converted.

To avoid your error, you can put the ISNUMERIC() on an inner derived table, so that you only try to CONVERT() data that is numeric in your outer WHERE clause.

answered Dec 9, 2014 at 16:57

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

1 Comment

Sean Lange Over a year ago

It might also be pointed out that ISNUMERIC is really pretty awful. It will return 1 for a lot of things that are very loosely numeric (ex. $1.5, 1e4, 0x1)

Alejandro García · Accepted Answer · 2014-12-10 11:48:58Z

0

Because there are one or more records that can't be converted as requested. If you run your query in SSMS it will execute the conversion in the select clause only for those rows displayed from the resultset, meanwhile the where clause is executed for all rows in the resultset to determine which rows are included in the resulset. To test this you can execute the query (without the where clause) and scroll down: once you reach the row that is messing you you will get the conversion error.

Hope this answers your question and explains that behavior.

answered Dec 10, 2014 at 11:48

Alejandro García

712 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting varchar to data type numeric ONLY in WHERE

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related