ISNULL() What Really Happens

[tag]T-SQL[/tag] [tag]ISNULL()[/tag] function: ISNULL ( check_expression , replacement_value )

This is just ensure that we are all aware of exactly how the ISNULL() function in T-SQL works….this may be known to most of you already but for those like myself who didn’t its worth knowing. I always believed that if the check_expression  was NULL then we simply compared against the replacement_value,this isn’t quite the case, the replacement_value is in fact converted to the type of the check_expression.

The example below will illustrate how the function does works:

SET @A = 'My example field for test'


SET @C = 'My example field for test'


  1. @B is NULL ( So we use the replacement_value)
  2. @C is converted to the same datatype as @B (This results in the loss of 5 chars, see declare statements)
  3. The comparison fails (‘My example field for test’! = ‘My example field for’)
  4. Select statement returns nothing
  1. #1 by Brian on May 3, 2007 - 8:21 pm

    Respectfully, this is right there in the Books Online page for the function. “Return Type: Returns the same type as check_expression.”

  2. #2 by moconnor on May 4, 2007 - 9:09 am

    Yes it is there and probablly in ten’s of other places across the web but it doesn’t take away from the fact that this is still worth knowing.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: