|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 WeilageYou 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 GONow 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. KennedyAha! 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 600Our 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.00But 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.
Last updated 22 May 2009