
DECLARE @var int
SET @var = NULL
select ISNULL(@var, 100) as [Var]
This returns 100. Simple and easy, right? ISNULL is often used to take care of NULLs in mathematical sets. For example:
SELECT AVG(ISNULL(Charge, 0))
FROM BankAccount
This will change all NULLs to 0, so we can use them in the AVG function. (Remember, a NULL in any mathematical function will return NULL for the result). But what about something like this:
DECLARE @feathers int
SELECT @feathers = ISNULL(featherCount,0)
FROM Bird
WHERE BirdID = 17
That’s fine if the featherCount for BirdID=17 is a number, or is null. But what if there is no row with BirdID = 17? @feathers will NOT be set to 0, as you might expect. It will be = NULL.
Bottom line: ISNULL returns NULL, not the specified value, when 0 rows are returned.
-Jennifer McCown, http:/www.MidnightDBA.com
Pingback: “Thing IS NOT NULL” isn’t the same as “Thing != NULL” | The MidnightDBA Star-Times