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.
|
|