T-SQL ISNULL() 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’
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)
- @B is NULL ( So we use the replacement_value)
- @C is converted to the same datatype as @B (This results in the loss of 5 chars, see declare statements)
- The comparison fails (’My example field for test’! = ‘My example field for’)
- Select statement returns nothing
Respectfully, this is right there in the Books Online page for the function. “Return Type: Returns the same type as check_expression.”
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.