The Uses of Coalesce in SQL Server

Let's start with the documented use of coalesce. According to MSDN, coalesce returns the first non-null expression among its arguments.
For example,

SELECT COALESCE(NULL, NULL, NULL, GETDATE())

will return the current date.  It bypasses the first NULL values and returns the first non-null value.

Using Coalesce to Pivot
If you run the following statement against the AdventureWorks database

SELECT Name FROM HumanResources.Department WHERE (GroupName = 'Executive General and Administration')

you will come up with a standard result set such as this.

If you want to pivot the data you could run the following command.

DECLARE @DepartmentName VARCHAR(1000) SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'  FROM HumanResources.Department WHERE   (GroupName = 'Executive General and Administration') SELECT @DepartmentName AS DepartmentNames

and get the following result set.

Using Coalesce to Execute Multiple SQL Statements
Once you can pivot data using the coalesce statement, it is now possible to run multiple SQL statements by pivoting the data and using a semicolon to separate the operations. Let's say you want to find the values for any column in the Person schema that has the column name “Name”. If you execute the following script it will give you just that.

DECLARE @SQL VARCHAR(MAX) CREATE TABLE #TMP    (Clmn VARCHAR(500),      Val VARCHAR(50)) SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP  Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn,  Name FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '];' AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS  JOIN sysobjects B ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME WHERE COLUMN_NAME = 'Name'     AND xtype = 'U'     AND TABLE_SCHEMA = 'Person' PRINT @SQL EXEC(@SQL) SELECT * FROM #TMP DROP TABLE #TMP

here is the result set.

My personal favorite is being able to kill all the transactions in a database using three lines of code. If you have ever tried to restore a database and could not obtain exclusive access, you know how useful this can be.

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.

Next Steps

• Whenever I think I may need a cursor, I always try to find a solution using Coalesce first.
• I am sure I just scratched the surface on the many ways this function can be used. Go try and see what all you can come up with. A little innovative thinking can save several lines of code.