The Many Uses of Coalesce in SQL Server
July 7th 2008 06:30
The Many Uses of Coalesce in SQL Server
Problem
Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.
Solution
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.
Name
1.Human Resources
2.Finance
3.Information Services
4.Facilities and Maintenance
5.Executive
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.
DepartmentNames
1.Human Resources;Finance;Information Services;Facilities and Maintenance;Executive
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.TA BLE_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.
Clmn Val
260 Person.Country.Region.Name Virgin Islands,US
261 Person.Country.Region.Name Wallis and Futuna
262 Person.Country.Region.Name Yemen
263 Person.Country.Region.Name Zambia
264 Person.Country.Region.Name Zimbabwe
265 Person.State.Province.Name Ain
.
.
.
.
271 Person.Country.Region.Name Alpes
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.
Messages
Kill 52;Kill53;Kill54
Problem
Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.
Solution
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.
Name
1.Human Resources
2.Finance
3.Information Services
4.Facilities and Maintenance
5.Executive
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.
DepartmentNames
1.Human Resources;Finance;Information Services;Facilities and Maintenance;Executive
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.TA BLE_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.
Clmn Val
260 Person.Country.Region.Name Virgin Islands,US
261 Person.Country.Region.Name Wallis and Futuna
262 Person.Country.Region.Name Yemen
263 Person.Country.Region.Name Zambia
264 Person.Country.Region.Name Zimbabwe
265 Person.State.Province.Name Ain
.
.
.
.
271 Person.Country.Region.Name Alpes
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.
Messages
Kill 52;Kill53;Kill54
| 72 |
| Vote |
Add To: 
