Should I check for null or dbnull in the query

Question

I have a query which checks how many records of a certain type are there using count().

select count(*) from abc where date="some value"

Here, is it possible that my query returns null or DBNull? Should I check for them?

A wise person once said, "an experiment is worth a thousand expert opinions." Try it out, try `where date="some value you know is not present"` — MushinNoShin, Dec 03 '12 at 14:45

score 5 · Answer 1 · answered Dec 03 '12 at 14:45

5

I don't think so: it can return zero or greater, since you're counting.

NULL would be a wrong result, since no results is there're zero results.

answered Dec 03 '12 at 14:45

Matías Fidemraizer

63,804
18
124
206

score 3 · Answer 2 · answered Dec 03 '12 at 14:45

3

No, it will always return a number greater or equal to 0.

answered Dec 03 '12 at 14:45

juergen d

201,996
37
293
362

score 1 · Accepted Answer · answered Dec 03 '12 at 15:06

1

Just try it:

SELECT COUNT(*) WHERE 1=2
--Returns 0

Per MSDN:

COUNT always returns an int data type value.

answered Dec 03 '12 at 15:06

Eren Ersönmez

38,383
7
71
92

score 0 · Answer 4 · answered Dec 03 '12 at 14:47

0

If you are using ExecuteScalar, I think the call will retrieve null instead of a DbNull object.

What I use to do in order to always receive a value is encapsulating my request: SELECT ISNULL((SELECT COUNT(date) FROM abc WHERE date = "some value"), 0)

Serge

answered Dec 03 '12 at 14:47

Serge Bollaerts

324
2
6

I can't understand which is the case where you'll get `null` when you're counting. – Matías Fidemraizer Dec 03 '12 at 14:48
`count(*)` is always >= 0 and never `DBNull` or `null`. This query will always return one row! – Olivier Jacot-Descombes Dec 03 '12 at 14:49

score 0 · Answer 5 · edited May 23 '17 at 12:27

0

You should check DbNull as it is database oriented.

See this answer

edited May 23 '17 at 12:27

Community

1
1

answered Dec 03 '12 at 14:48

charles

55
8

score 0 · Answer 6 · answered Dec 03 '12 at 14:54

0

This is what i do:

if (rdr.HasRows)
        {
          rdr.Read();
          if (rdr["MyField"] != DBNull.Value)
                {
                   bla bla....
                }

So yes check for DBNull.

answered Dec 03 '12 at 14:54

e4rthdog

5,103
4
40
89

Should I check for null or dbnull in the query

6 Answers6