~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Zadoyen, Eva" <EZadoyen@s...>
Subject: [sql_language] Re: COALESCE


: Thanks for the reply, David!
:
: I'm just wondering what the advantage of using COALESCE() instead of
ISNULL
: in the discussed case - I don't see much advantage of it.
: If I'm wrong - please explain, because I have a lot of stored procedures
: that have similar coding and if it's a significant - I would change them
in
: favor of COALESCE().

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In your particular case there is no real advantage to using COALESCE() over
ISNULL()
But there is no disadvantage either...

COALESCE() is "better" IMHO because:
a) it's part of the ANSI 92 SQL standard (whereas ISNULL() isn't). Thus, it
is supported in many other RDBMS products other than SQL Server. If you ever
have to work with DB2, or Oracle or similar, you will be better off if you
are aware of, and use, what's in the SQL standard

b) it's more flexible than ISNULL(). COALESCE() accepts multiple values,
rather than just two. I've only ever had to use this once so far, but the
ability to compare *two* fields, and then return an arbitrary value if both
of these two fields were NULL was very helpful in this case.

Basically COALESCE() does everything that ISNULL() does, and more. Plus,
it's part of the SQL standard. So, there's no real reason not to use it
(IMHO).

Cheers
Ken