Interesting Tidbit on ISNULL()

ISNULL() replaces NULL with the value you provide. For example:  

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