CODEWITHSUNDEEP
sqlsql-servert-sql
Nassim Hafici
Nassim Hafici

Reputation: 460

Force COALESE(NULL,NULL) to return NULL

In SQL SERVER Considering the fact that: Col1 and Col2 contain numeric and NULL values

SELECT 
COALESCE(Col1,Col2)

Return an error: "At least one of the arguments to COALESCE must be an expression that is not the NULL constant." Considering that Col1 and Col2 are NULL,I want to force it to return NULL value in this case.

The workaround seems to be unelegant/inefficient for me:

SELECT
NULLIF(COALESCE(Col1 ,Col2 ,''),'')

Note that Col1 and Col2 are numeric fields and cannot take '' as a value.

Any other suggestion ?

Thank you for your help

Upvotes: 2

Views: 421

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This code works:

SELECT COALESCE(Col1, Col2) 
FROM . . .   -- references here that define COL1 and COL2

If both columns are NULL, it will return NULL (typed as an integer in your case).

The only time you get the error you mention is when the columns are explicitly NULL. Even calculations seem to get around this:

select coalesce(null + 3, null)
--> NULL rather than an error

The following even returns NULL rather than an error:

declare @col1 int;
declare @col2 int;

select coalesce(@col1, @col2);

Here is a db<>fiddle.

Upvotes: 5

Related Questions