Use of "Coalesce" in SQL Server 2008

Use of "Coalesce" in SQL Server - efficent way to script and avoid mutiple Case statements and ISNULL.

COALESCE - Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression - Is an expression of any type.

n - Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types - Returns the same value as expression.

Remarks - If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

Simple real life example from MSDN - http://msdn.microsoft.com/en-us/library/aa258244(SQL.80).aspx

Other many uses as well - http://www.mssqltips.com/tip.asp?tip=1521

(Fav one from this article = was to KILL all transactions forecfully that are locked and needed to be released before restoring the database -

------------------------------------------
DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('AdventureWorks')

PRINT @SQL --EXEC(@SQL)

Replace the print statement with exec to execute will give you a result set such as the following.
-----------------------------------------------

Thanks - D