2006/08/09

ISNULL, implicit conversion and integer data type

SELECT ISNULL(CAST(NULL as int), '+')
0

SELECT ISNULL(CAST(NULL as int), '-')
0

SELECT ISNULL(CAST(NULL as int), '/')
Server: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '/' to data type int.

To explain the above one must to remember that:
  1. ISNULL returns the same type as the check expression (that's the 1st parameter),
  2. varchar and char data types can be implicitly converted to int (if you don't know what it means, try executing SELECT 2 + '2'),
  3. SQL Server treats '+' and '-' characters as a number - 0.
So this is what happens - ISNULL tries to return int data type and it converts the replacement value (that's the 2nd parameter); it succeeds in 2 cases, but fails in the 3rd one, because '/' is not a numeric expression.

No comments:

Post a Comment