Search This Blog

Monday, May 4, 2009

COALESCE function instead of long CASE WHEN ... ELSE (T-SQL)

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:

SELECT TheSource =
COALESCE(localSource, intranetSource, internetSource, '')
FROM ...

Popular Posts