[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'
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