DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use COALESCE instead of ISNULL?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the COALESCE function.

-- SQL coalesce - ANSI-92 SQL standard - SQL isnull - special in T-SQL

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,

City,

Region=COALESCE(Region,''),

PostalCode, Country, Phone,

Fax=COALESCE(Fax, 'NO FAX')

FROM Northwind.dbo.Customers

ORDER BY CompanyName

GO

/* Partial results

 

City        Region      PostalCode  Country     Phone             Fax

Kobenhavn               1734        Denmark     31 12 34 56       31 13 35 57

Paris                   75016       France      (1) 47.55.60.10   (1) 47.55.60.20

Lander      WY          82520       USA         (307) 555-4680    (307) 555-6525

Charleroi               B-6000      Belgium     (071) 23 67 22 20 (071) 23 67 22 21

Portland    OR          97201       USA         (503) 555-3612    NO FAX

*/

------------

 

------------

-- SQL coalesce for comma-limited list building

------------

DECLARE  @EmpList VARCHAR(128);

SELECT   TOP 7 @EmpList =

  coalesce(@EmpList + ', ','') + cast(EmployeeID AS VARCHAR(4))

FROM     AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

 

SELECT EmployeeIDCSV = @EmpList

/* Result

 

EmployeeIDCSV

1, 2, 3, 4, 5, 6, 7

*/

------------

 

------------

-- Using coalesce in view definition

------------

-- SQL coalesce

-- SQL view\

-- SQL inner join

USE AdventureWorks

IF object_id(N'Person.vEmployeeName', 'V') IS NOT NULL

DROP VIEW Person.vEmployeeName

GO

CREATE VIEW Person.vEmployeeName

AS

SELECT FirstName,

MiddleName,

LastName,

Name=LastName + ', ' + FirstName + COALESCE(' ' + MiddleName, '')

FROM Person.Contact c

INNER JOIN HumanResources.Employee e

ON c.ContactID = e.ContactID

GO

 

SELECT * FROM Person.vEmployeeName

ORDER BY LastName, FirstName

GO

 

/* Partial results

 

FirstName   MiddleName  LastName    Name

Syed        E           Abbas       Abbas, Syed E

Kim         B           Abercrombie Abercrombie, Kim B

Hazem       E           Abolrous    Abolrous, Hazem E

Pilar       G           Ackerman    Ackerman, Pilar G

Jay         G           Adams       Adams, Jay G

François    P           Ajenstat    Ajenstat, François P

*/

------------

 

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE