In SQL Server, if you’re getting error Msg 4127 that reads “At least one of the arguments to COALESCE must be an expression that is not the NULL constant”, it’s probably because all of your arguments to the COALESCE() expression are the NULL constant.

To fix this issue, make sure at least one argument is not the NULL constant.

Example of Error

Here’s an example of code that produces this error:

SELECT COALESCE( null, null );

We get this:

Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

In this case, all arguments were the NULL constant, and so an error was returned.

Solution

The solution is easy. All we need to do is make sure at least one argument is not the NULL constant:

SELECT COALESCE(null, 'Cat', 'Dog');

Result:

Cat

In this case, Cat was the first non-NULL argument, and so COALESCE() returned that value.

As seen here, it’s OK to include the NULL constant as an argument, as long as there’s also at least one other argument that is not the NULL constant.

NULL Expressions & Database Columns

Note that the NULL constant is not the same as an expression that results in NULL. And it’s not the same as a database column that contains NULL.

For example, if all arguments reference database columns, and those database columns are NULL, then we don’t get the error.

Suppose we run the following query:

SELECT CustomerId, CustomerCategoryId
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

CustomerId  CustomerCategoryId
----------- ------------------
NULL        NULL

Both columns contain NULL values.

So if we pass both columns to COALESCE(), we get a result of NULL:

SELECT COALESCE( CustomerId, CustomerCategoryId )
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

NULL

The same is true if we replace one of the columns with the NULL constant:

SELECT COALESCE( CustomerId, null )
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

NULL

So it’s only when all arguments are the NULL constant that we get an error.