SQL Server COALESCE函数详解及实例

COALESCE ( expression [ ,...n ] ) 

CREATE TABLE #CheckSumTest  
   ( 
     ID int identity , 
     Num int DEFAULT ( RAND() * 100 ) , 
     RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY 
   ); 

SELECT COALESCE(NULL, NULL, GETDATE()) 

SELECT COALESCE(NULL, NULL, NULL) 

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

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

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 

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