Creativity with COALESCE()

from the Artful SQL Server & Access Tips List


The COALESCE() function accepts a series of values and a value to use in the event that all items in the list are null, and returns the first not-null value. For example, suppose you have a table of persons whose columns include FirstName, MiddleName and LastName. Suppose this table contains values such as

     John A. MacDonald
     Franklin D. Roosevelt
     Madonna
     Cher
     Mary Weilage

You want to print their complete names as single strings. Here’s how you’d do it with COALESCE():

SELECT  FirstName + ‘ ‘ + COALESCE(MiddleName,’’) + ‘ ‘ + COALESCE(LastName,’’)

But do you really want to write that for every query? Why not turn it into a function?

CREATE FUNCTION WholeName
(
     -- Add the parameters for the function here
     @first varchar(50),
     @middle varchar(50),
     @last varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
     -- Declare the return variable here
     DECLARE @Result varchar(50)
     -- Add the T-SQL statements to compute the return value here
     SELECT @Result = @first + ' ' + COALESCE(@middle,'') + ' ' + COALESCE(@last,'')
     -- Return the result of the function
     RETURN @Result
END
GO

Now whenever you need this, 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')
UNION
SELECT dbo.WholeName( 'Cher',NULL,NULL )
UNION
SELECT dbo.WholeName( 'John', 'F.', 'Kennedy')

Here is the result set:

Cher 
James  Bond
John F. Kennedy

Aha! A whole in our thinking: there’s a double-space in James Bond’s name. (Double-space seven, they call him.) Fortunately, it’s not hard to fix this. We just change the @result line to:

SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')

Here’s another use of COALESCE(), this time to produce a list of monies paid to employees. Our problem is that some employees work by the hour, some by piece work, some by straight salary and some by commission.
Here is the code to create a sample table:

CREATE TABLE [Coalesce_Demo].[PayDay](
     [EmployeeID] [int] NOT NULL,
     [HourlyWage] [money] NULL,
     [HoursPerWeek] [int] NULL,
     [AmountPerPiece] [money] NULL,
     [PiecesThisWeek] [int] NULL,
     [WeeklySalary] [money] NULL,
     [CommissionThisWeek] [int] NULL,
 CONSTRAINT [PK_PayDay] PRIMARY KEY CLUSTERED
(
     [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Here are a few sample rows, one of each type:

1    18.00     40     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

Our problem is to list the amount paid to the employees, regardless of how they are paid, in a single column. Here’s how to do it:

SELECT
     EmployeeID,
     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

But we might need that expression in several places in our application, and although it works, it’s a little less graceful than we’d like. I see little advantage in creating a function to do it. But a calculated column; now there’s an idea:

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.

Return to the Artful SQL Server & Access Tips page