A new understanding of coalesce () Functions

People often encounter coalesce functions, and most of them regard them as a more effective form of isnull. In fact, I found that it is a very useful function, but there are only a few documents. In this article, I will show you the basic usage of coalesce and some features you may have never seen before.

Let's start with the use of coalesce with documented records. According to msdn, coalesce returns the first non-null expression in its parameters.

For example,

　　

Select coalesce (null, getdate ())

It returns the current date. It skips the first null value and returns the first non-null value.

 

Use coalesce for perspective

If you run the following statement on the adventureworks database:

　　

Select name From HumanResources. Department Where (groupname = 'executive general and admin ')

You will get a standard result set like below.

 

Figure 1

If you want to view data, you can run the following command.

　　

Declare @ departmentname varchar (1000) Select @ departmentname = coalesce (@ departmentname, '') + name + ';' From HumanResources. Department Where (groupname = 'executive general and admin ') Select @ departmentname as departmentnames

And obtain the following result set.

 

Figure 2

Use coalesce to execute multiple SQL statements

If you can use the coalesce statement to view data, you may run multiple SQL statements by pivot data and using a semicolon to separate these operations. Suppose you want to find the value of any field named "name" in person schema. If you execute the following script, it will play this role.

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

The following is the result set.

Figure 3

I personally like to use three lines of code to kill all transactions in the database. If you have tried to recover the database and cannot obtain special access, you will know how useful this is.

　　

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

This produces a result set similar to the following.