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:

DECLARE @A VARCHAR (25)
SET @A = 'My example field for test'

DECLARE @B VARCHAR (20)
SET @B = NULL

DECLARE @C VARCHAR (25)
SET @C = 'My example field for test'

SELECT *
FROM TEST
WHERE @A = ISNULL(@B,@C)

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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: