< turn >sql function--coalesce

Reprint Address: http://blog.csdn.net/dba_huangzj/article/details/8300784#

Directory

1. First look at the brief definition of Books Online
2. Grammar
4. Here are a few more useful examples
1. First take a look at how this function is used on MSDN COALESCE function returns a value that is not NULL in a parameter, such as
2. Then take a look at applying the function to pivot. The following statement runs on the AdventureWorks database
3. If you want to reverse the result you can use the following statement
4. Using functions to execute multiple SQL commands
5. There is also a very important feature when you try to restore a library and find that it is not exclusive access when this feature is very effective we can open multiple windows to simulate multiple connections and execute the following script

Many people know the IsNull function, but few people know that the COALESCE function, people will inadvertently use the COALESCE function, and found that it is more powerful than isnull, in fact, so far, this function is indeed very useful, this article mainly explains some of the basic use:

first look at the brief definition of Books Online:

Returns the first non-empty expression in its argument

Syntax:

expression [ ,

If all parameters are null, then COALESCE returns NULL. There should be at least one null value for the null type. Although ISNULL is equivalent to coalesce, their behavior is different. An expression containing a ISNULL with a non-null argument is treated as NOT null, and an expression containing a coalesce with a non-null argument is treated as null. In SQL Server, to create an index on an expression containing a coalesce with a non-null argument, you can use the PERSISTED column property to persist the computed column, as shown in the following statement:

[SQL]View Plaincopyprint?

1. create table #CheckSumTest    
2.      ( 
3.           ID int identity, 
4.           Num int default (RAND () * +),  span>
5.          rowchecksum AS Span class= "func" >coalesce (CHECKSUM (ID, num), 0) PERSISTED primary key&n Bsp
6.     );  

CREATE TABLE #CheckSumTest      (         ID int identity,         Num int DEFAULT (RAND () *),         rowchecksum as COALESCE ( CHECKSUM (ID, num), 0) PERSISTED PRIMARY KEY     );

Here are a few more useful examples:

first, take a look at the use of this function from MSDN, the COALESCE function (hereinafter referred to as the function), and return a non-null value for a parameter. such as:

[SQL]View Plaincopyprint?

1. SELECT COALESCE (null, null, GETDATE ())

SELECT  COALESCE (null, NULL, GETDATE ())

Since all two parameters are null, the value of the GETDATE () function is returned, which is the current time. That is, the first non-null value is returned. Since this function returns the first non-null value, there must be at least one non-null value inside the parameter, and if you use the following query, you will get an error:

[SQL]View Plaincopyprint?

1. SELECT COALESCE (null, null, null)

SELECT  COALESCE (null, NULL, NULL)

then take a look at applying the function to pivot, and the following statement runs on the AdventureWorks database:

[SQL]View Plaincopyprint?

1. SELECT Name
2. From HumanResources.Department
3. WHERE (groupname= ' Executive generaland Administration ')

SELECT  Name from    humanresources.department WHERE   (groupname= ' Executive generaland Administration ')

will get the following result:

If you want to reverse the result, you can use the following statement:

[SQL]View Plaincopyprint?

1. declare @DepartmentName varchar (  
2.    
3. select  @ Departmentname = coalesce (@DepartmentName, name + '; '  
4. from    humanresources.department 
5. where   (groupname= Span class= "string" > ' Executive Generaland administration ')  
6.    
7. select  @DepartmentName as departmentnames 
8.    

DECLARE @DepartmentName VARCHAR (+)   SELECT  @DepartmentName = COALESCE (@DepartmentName, ') + Name + '; ' From    humanresources.department WHERE   (groupname= ' Executive generaland Administration ')   SELECT  @ Departmentname as Departmentnames  

use functions to execute multiple SQL commands:

Once you know that this function can be reversed, you should also know that it can run multiple SQL commands. and use semicolons to differentiate between individual operations. The following statement is the value of a column named name under the person schema:

[SQL]View Plaincopyprint?

2. DECLARE @SQL VARCHAR (MAX)
6. CREATE TABLE #TMP
8. (CLMN VARCHAR),
10. Val VARCHAR ()
14. Select @SQL =COALESCE (@SQL,") +CAST (' INSERT into #TMP Select ' +Table_schema + '. ' + Table_   NAME + '. '
16. + column_name + 'as CLMN, NAME from ' + Table_schema + '. [' + table_name +
18. ‘];‘ as VARCHAR (MAX))
20. From INFORMATION_SCHEMA. COLUMNS
22. JOIN sysobjects B on information_schema. COLUMNS. table_name = B.NAME
24. WHERE column_name = ' NAME '
26. and xtype = ' U '
28. and Table_schema = ' person '
32. PRINT @SQL
34. EXEC (@SQL)
38. SELECT * from #TMP
40. DROP TABLE #TMP

DECLARE @SQL VARCHAR (MAX)   CREATE TABLE #TMP     (clmn varchar ($),       Val varchar ())   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

There is also a very important function:. This feature works well when you try to restore a library and find that it cannot be accessed exclusively. Let's open multiple windows to simulate multiple connections. Then execute the following script: [SQL]View Plaincopyprint?

1. declare @SQL varchar (8000)  
2.    
3. select  @SQL = coalesce (@SQL, ' Kill ' + cast (spid span class= "keyword" >as varchar (ten) +
4. from     sys.sysprocesses 
5. where   DBID = db_id ( Span class= "string" > ' AdventureWorks ')  
6.    
7. print @ SQL --exec (@SQL) Replace The print statement with EXEC to execute 

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

The results are as follows:

Then you can copy the results and kill all the sessions at once.