TRY_CAST() Arithmetic Overflow Error – How?

It is expected that at some point you will receive some bad data – such as the wrong range, form or data type. Sometimes you receive valid data but the result of a calculation is when it goes wrong, a simple multiplication takes your data from good to bad, the difference between your cube processing and not processing, the difference between your clients receiving vital data and not receiving anything at all. Therefore, it makes sense to introduce TRY_CAST() as a validation step to improve robustness – instead of an error, you will get NULL.

Context

INT datatype – a number in the range -2,147,483,648 to 2,147,483,647

BIGINT datatype – a number in the range -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807)

 

TRY_CAST() In Action

TRY_CAST() seems like the ideal debugging tool for issues with data types. In particular, this blog looks at the overflow error found when handling numbers in SQL – INTs and BIGINTs. I used this function in my WHERE clause when trying to identify the rows where the result of a simple multiplication causes the error.

This is simply as follows…

SELECT *

FROM dbo.table1

WHERE TRY_CAST(COLUMN1 * COLUMN2 AS INT) IS NULL

This should have returned the rows where the result of the multiplication exceeded the limit of the INT datatype. This is because the TRY_CAST() function attempts to CAST a value and if it can, then the cast is successful but if it can’t, instead of throwing an error, it sets the value to NULL. You can read more about the function here.

 

TRY_CAST() Error

Rather than receiving the erroneous rows in a result set, I got the following error message:

Arithmetic overflow error converting expression to data type int.

This suggests that the result is too large a value for the datatype INT. However, my code is not asking that the results are displayed, nor do I require a complete CAST action. How am I seeing an error for function that switches an error for a NULL?

Although no results are to be displayed or even an explicit and complete CAST, the calculation is still performed and the result is calculated. This result is stored in SQL Server, however temporary, and it has to be stored as a datatype. This datatype is inherited from the operands of the calculation, in this case column1 and column2 are INTs so the result is assumed to be an INT. However, if column1 is 3,500,000 and column2 is 1000, the upper threshold of what an INT can store is exceeded and therefore fails, giving the arithmetic overflow error.

The Solution

As the result inherits the datatype from the operands, we need to CAST the operands within the calculation. We want to store a very large number so we CAST at least one of the operands to a BIGINT. And so our code becomes the following:

SELECT *

FROM dbo.table1

WHERE TRY_CAST( (CAST(COLUMN1 AS BIGINT)* COLUMN2) AS INT) IS NULL

You only need to CAST one operand as SQL Server assumes the most accommodating datatype.

Tags: ,