Someone recently asked me something like “Is ISNULL or COALESCE better to use?”.
There is semantically very little difference. COALESCE is ANSI-standard and ISNULL is Microsoft implementation.
Both returns the first non-null value. ISNULL accepts a total of 2 parameters and COALESCE accepts a total of at least 256 parameters.
However, there is a difference in the result!
ISNULL uses the datatype from the first parameter and returns a result with the same datatype.
COALESCE uses data type precedence and uses the datatype with the highest precedence.
Why is this important? You can create a bug when using ISNULL or even crash your application!
Wait until last example.
Consider this sample setup.
Now let’s see what the result and datatypes are.
As you can see, you will get the expected result. What about the datatypes used?
ISNULL uses the datatype for the first parameter and COALESCE uses the datatype with highest precedence.
“Where is the bug?” you ask. I’ll show you.
Let @i be NULL in the first sample data.
Whoopsie. Now you made your application crash!
Let use remove all columns using variable @s.
And there is the bug. Since @i is NULL, the function ISNULL returns the second parameter but still using the datatype for the first parameter.
Whoops.
What happens if we use COALESCE instead?
No crash. Correct result.
On the other hand, we can use COALESCE and still crash your application even if the first parameter is not null!
