NullReferenceException using COALESCE in SQL query

Question

Perhaps I'm misunderstanding COALESCE, but in essence, what I'm trying to do is run a select query that if it returns NULL instead of an int, instead return 0 for the purposes of ExecuteScalar().

SQL Server query:

SELECT TOP 1 COALESCE(SeqNo,0) 
FROM tblProjectChangeOrder 
WHERE ProjectID = XXXXX 
ORDER BY SeqNo DESC

If the supplied ProjectID exists in the Change Order table, it returns the expected highest SeqNo. However, if the supplied ProjectID has no existing Change Orders (thus returns NULL for SeqNo), rather than the COALESCE returning 0, I am still getting NULL.

Am I just getting the syntax wrong or is what I want to do possible with COALESCE? The other option I see is to have my ExecuteScalar() pass to a nullable int, then follow that with a ?? to coalesce in my C# codebehind.

You might want to re-read what COALESCE does. If there are no rows, why do you expect rows? COALESCE operates on a row level. — ProgrammingLlama, Apr 09 '18 at 15:44
Why not simply handle it client-side? It's a perfect candidate for a `if(something==DBNull.Value) something=0;` — Alejandro, Apr 09 '18 at 15:51
@Sami he's appears to be doing ExecuteScalar on zero rows (the project isn't in the change table) — ProgrammingLlama, Apr 09 '18 at 15:51
It looks like your trying to return the last seq number. Why not do a max on it? — Kevbo, Apr 09 '18 at 15:51
Have a look at this question: https://dba.stackexchange.com/questions/165295/why-coalesce-within-a-subquery-returns-null — McNets, Apr 09 '18 at 15:54

Thom A · Accepted Answer · 2018-04-09T15:59:25.123

5

As john has mentioned in the comments, COALESCE operates at row level. If a table contains no rows, or a statement returns no rows, then no rows will be returned. Take the simple example below:

CREATE TABLE #Sample (ID int);

SELECT COALESCE(ID, 0)
FROM #Sample;

DROP TABLE #Sample;

Notice that nothing is returned.

Instead, one method is to use a subquery. For your query, that would result in:

SELECT COALESCE(SELECT TOP 1 SeqNo 
                FROM tblProjectChangeOrder 
                WHERE ProjectID = XXXXX 
                ORDER BY SeqNo DESC),0) AS SeqNo;

This also assumes that Seqno has a data type of int; otherwise you're likely to get a conversion error.

edited Apr 09 '18 at 15:59

answered Apr 09 '18 at 15:50

Thom A

88,727
11
45
75

1

Nice. Select max(SeqNo) would be as little shorter. – paparazzo Apr 09 '18 at 16:14
Didn't know Select Max() was a thing. Makes that a lot cleaner. `SELECT COALESCE(( SELECT MAX(SeqNo) FROM tblProjectChangeOrder WHERE ProjectID = XXXXX),0) AS SeqNo` – Jeff Cox Apr 09 '18 at 16:58
ISNULL is better than COALESCE here. COALESCE will run the subquery twice – Martin Smith Apr 14 '18 at 21:18

Nerevar · Answer 2 · 2018-04-09T16:08:37.500

My guess is that the null reference exception occures on the code and has nothing to do with the sql query. It might just be that your code is not handling that you return no rows (or no scalar in your case) but you might be trying to access it somewhere in c#.

Show us the line of code that is throwing this exception in c# so we might be able to confirm this.

regards

Edit : From this similar topic

In your c# code you might want to try ("cmd" being your "SqlCommand" object):

int result = 0;
int.TryParse(cmd.ExecuteScalar(), out result);

or in one line

int.TryParse(cmd.ExecuteScalar(), out int result);

I don't know if it is the most suitable solution for you but I hope it is a start.

paparazzo · Answer 3 · 2018-04-09T16:10:07.717

As covered null and no row are not the same.
This sample covers it.

set nocount on;
select isnull(1, 0) 
where 1 = 1;
select isnull(1, 0) 
where 1 = 2;
select isnull(null, 0) 
where 1 = 1;
select isnull(null, 0) 
where 1 = 2;

-----------
1


-----------


-----------
0


-----------

this should work

select top 1 isnull(seq, 0) 
from (select null as seq 
      union all   
      select max(seq) from tblProjectChangeOrder where ProjectID = XXXXX 
     ) tt
order by seq desc

NullReferenceException using COALESCE in SQL query

3 Answers3