Wednesday, October 20, 2010

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.

COALESCE ( expression [ ,...n ] )

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:

WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expressionN IS NOT NULL) THEN expressionN

Simple real life example from MSDN -

Other many uses as well -

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


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


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

Thanks - D

No comments: