In SQL, if you’re not sure if a value can be cast to a certain datatype there are some built-in functions that can help you out.
Take the following construction, where col_Value is a CHAR(4):
At first glance, this solution seems to handle col_Value just fine. If col_Value contains a numeric value it will be cast to an INT and if it’s not, a NULL value will be returned.
The developers expectations seem to be met: in this scenario no typecasting errors can occur. Or can they?
Truth is, the ISNUMERIC function is often misinterpreted (at least, that’s my personal experience).
According to MSDN the definition of ISNUMERIC is: Determines whether an expression is a valid numeric type.
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type.
The catch is, a returnvalue of 1 means that the expression can be cast to at least one numeric datatype, rather than all available numeric datatypes supported by SQL Server.
The following queries illustrate this behavior:
So in conclusion; if an expression has to be cast into a specific numeric datatype, checking ISNUMERIC may not be sufficient. This is of course heavily dependant on the possible values of the expression, but if this is unpredictable extra checks may be necessary