The Caveat of IsNull()

IsNullI() is a very handy function to replace the value of an expression with another, if the expression being tested is NULL. However, this function should be use with care. Remember always that the expression being checked and the replacement value should have the same data type or some unexpected (and sometimes undetectable) result may occur. As a rule, the function will return an expression with the same data type of that of the expression being checked.

Considering the following code:

declare @a as bit
select
 test_result = 
    case 
      when isnull(@a, 9) = 9 then 'Nine'
      when @a = 1 then 'One'
      when @a = 0 then 'Zero'
      else  'Unknown'
    end

The above code will never return the value 'Nine', even if at first glance, the code above should return the string 'Nine'.

What's going on? It's because the variable @a is a bit data type. The function will explicitly convert the second expression into the data type of the first expression. On the sample code, 9 will be converted into a bit expression which will return 1. So the expression isnull(@a, 9) = 9 will always be false. The entire code will not return an error and is not immediate visible.

This can also happen with strings. Consider the following code:

declare @a as varchar(5)


select isnull(@a, 'abcdefghij')

The above code will return the first 5 characters ('abcde') only. Why? Because this time, the variable @a is 5-character string. So even if it looks like the entire replacement string should have been returned by the function, again, the second expression will be converted into the first expression resulting to data truncation.

So make sure to check the data types of those two parameters.

As always, comments are welcome...


~~ CK