In a previous post we discussed using functions within SQL Server. The SQL Coalesce function is used when you want to combine values from a series of columns or expressions when they might contain NULL values.
The goal of this demonstration is to use the COALESCE function to find the annual salary of each employee. The first section of code is creating a table with five columns to hold sample data.Next we use an INSERT statement to add 4 rows of data to our demonstration table.
Now that we have our data ready, it is time to demonstrate the COALESCE function. The purpose of this function is to look for and act on fields that may or may not have a NULL value. (A NULL value is an absence of a value or an unknown value.)
In the case of this statement, the COALESCE function will look to see if there is a value in the hourly_wage field. If there is a value then the function will calculate hourly_wage *40*52. If the hourly_wage field was NULL it would move on to the next argument, in this case the salary field.
If the salary field has a value then that value is returned. If the salary field also has a NULL value then the function would move to the next argument. In this case the commission*num_sales. This would continue until there were no more arguments to evaluate at which time the function would bring back a NULL response.