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.
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.
For Example
this experiement can do with your own database or with your table
Use Database Northwind
SELECT Name |
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)
|
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)
+ COLUMN_NAME + ''' AS Clmn, Name FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + |
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.
but this Adventure database is available in MSSQLSERVER2005
DECLARE @SQL VARCHAR(8000)
|
will give you a result set such as the following.