Friday, 30 March 2012

Coalesce in Sql server example

COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.

Here is a simple example: You have a table of persons whose columns include FirstName, MiddleName and LastName. The table contains these values:
  • John A. MacDonald
  • Franklin D. Roosevelt
  • Madonna
  • Cher
  • Mary Weilage
If you want to print their complete names as single strings, here's how to do it with COALESCE():
SELECT  FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' +COALESCE(LastName,'')
If you don't want to write that for every query, Listing A shows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actually named) just call the function and pass the three columns. In the examples below, I'm passing literals, but you can substitute column names and achieve the same results:
SELECT dbo.WholeName('James',NULL,'Bond')
 SELECT dbo.WholeName('Cher',NULL,NULL)
 SELECT dbo.WholeName('John','F.','Kennedy')
Here is the result set:
 James  Bond
 John F. Kennedy
You'll notice a hole in our thinking -- there are two spaces in James Bond's name. It's easy to fix this by changing the @result line to the following:
SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')
Here's another use of COALESCE(). In this example, I will produce a list of monies paid to employees. The problem is there are different payment arrangements for different employees (e.g., some employees are paid by the hour, by piece work, with a weekly salary, or by commission).
Listing B contains the code to create a sample table. Here are a few sample rows, one of each type:
1     18.0040    NULL  NULL  NULL  NULL
 2     NULL  NULL  4.00  400   NULL  NULL
 3     NULL  NULL  NULL  NULL  800.00      NULL
 4     NULL  NULL  NULL  NULL  500.00      600
Use the following code to list the amount paid to employees (regardless of how they are paid) in a single column:
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment
FROM [Coalesce_Demo].[PayDay]
Here is the result set:
EmployeeID  Payment
 1     720.00
 2     1600.00
 3     800.00
 4     1100.00
You might need that expression in several places in your application and, although it works, it isn't very graceful. This is how you can create a calculated column to do it:
ALTER TABLE Coalesce_Demo.PayDay
ADD Payment AS
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)
Now a simple SELECT * displays the pre-calculated results.


This tip demonstrates some unusual ways and places to apply the power of COALESCE(). In my experience, COALESCE() most often appears within a very specific content, such as in a query or view or stored procedure.
You can generalize the use of COALESCE() by placing it in a function. You can also optimize its performance and make its results constantly available by placing it in a calculated column.

1 comment:

  1. A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works.

    DECLARE @Exp1 varchar(30),
    @Exp2 varchar(30)

    SET @Exp1 = NULL
    SET @Exp2 = 'SQL Server'

    SELECT COALESCE(@Exp1,@Exp2)


    SQL Server
    The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,...)), but for the example presented in this article only two are needed.