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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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