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.