Coalesce in your SQL Server Stored Procedures

Chris Love

Last Updated - Sat Jan 09 2021

There are so many built-in functions and keywords in SQL Server to help our applications perform and the coding is so easy that many get lost in the breadth of everyday needs. A few months ago I came across a function that I did not recall seeing before, Coalesce.

Often we need to handle situations where we may have null values and the IsNull function just will not suffice. Coalesce is a function that offers a little more functionality than the IsNull function because you can pass it a series of values and it will return the first non-null value in the list. IsNull takes two parameters, the value you want to check and a default value if the first value is null.

The Coalesce function is equivalent to a CASE statement, with the ELSE case returning NULL. While the IsNull function returns a default value when it is passed a null value, the Coalesce does not. You could embed a Coalesce function inside an IsNull function to get a default value for null.

The example in the documentation shows how you could return the first non-null value of a commission structure for an employee. The following example returns the first Id of the college associated with the record.

COALESCE(CollegeId, JrCollegeId, ComunityCollegeId)

So if the CollegeId is null and the JrCollegeId has a value the JrCollegeId value is returned.

I hope you find this useful and can use it in your stored procedures one day!