CopyPaste.Net: COALESCE - Returns the first non-null expression among its arguments

COALESCE ( expression [ ,...n ] )

COALESCE returns the first non-null expression among its arguments.

If all arguments are NULL, COALESCE returns NULL.

Uses of Coalesce:

Get result of gradual condition:

SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM SalariesTable

Concatenate column results to a string:

declare @StrList nvarchar(max) = ''

update SrcTableName set @StrList = COALESCE(@StrList,'') + COALESCE(TableColumnName, '') + ','

select @StrList=left(@StrList,len(@StrList)-1)

select @StrList

Using Coalesce to Execute Multiple SQL Statements:

DECLARE @SQL nvarchar(max)

SELECT @SQL=COALESCE(@SQL,'') +'Kill '+CAST(spid AS nvarchar(10))+ '; '

FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'DBNAME'

PRINT @SQL --EXEC(@SQL)

COALESCE VS. ISNULL

ISNULL accepts exactly two parameters. COALESCE can take multiple parameters.
The result of ISNULL always takes on the datatype of the first parameter COALESCE works more like a CASE expression, which returns a single datatype depending on all possible outcomes.
COALESCE result can be null, ISNULL always will return not-null result.

COALESCE VS. CASE

COALESCE simply returns the first value out of a list that is not NULL. Case can consider more Complicated conditions.
The COALESCE SQL Server function is very useful in constructing queries that evaluate multiple input parameters and can often be much faster than CASE statements.

CopyPaste.Net