Those who prefer reading this site through RSS now can also subscribe for updates to .NET Tips & Tricks Community.
Instead of using long "SELECT ... CASE WHEN ... ELSE ..." construction, you can use the COALESCE function when you need to find a value that is not NULL. Lets review the following T-SQL expression, in which we need to select an available "source":
SELECT TheSource =
CASE
WHEN localSource IS NOT NULL THEN localSource
WHEN intranetSource IS NOT NULL THEN intranetSource
WHEN internetSource IS NOT NULL THEN internetSource
ELSE ''
END
FROM ...
Now lets rewrite the code above using COALESCE function:
COALESCE(localSource, intranetSource, internetSource, '')
The tip applies to MS SQL Server 2000/2005.