Why does IS NULL work but SQL Parameter with DBNull.Value for DateTime does not?

Question

I have a C# console application that is performing a database lookup on SQL Server 2014. It queries against a table with a DateTime column called EffectiveDate which allows nulls.

If I write a standard SQL query that uses 'WHERE EffectiveDate IS NULL', the results come back properly. If I change the WHERE clause to accept a parameter and set that parameter to DBNull.Value, the result is null.

Example 1 Using IS NULL -> resultObj has appropriate value:

    public void RetrieveFileType()
    {
        string sSQL = "SELECT [FileType]  " +
                      "FROM DTCC_APP_ProcessControl " +
                      "WHERE [EffectiveDate] IS NULL ";

        using (SqlConnection oConn = GetNewConnection())
        {
            using (SqlCommand cmd = new SqlCommand(sSQL, oConn))
            {
                object resultObj = cmd.ExecuteScalar();
            }
        }
    }

Example 2 Using DBNull.Value -> resultObj = null:

    public void RetrieveFileType()
    {
        string sSQL = "SELECT [FileType]  " +
                      "FROM DTCC_APP_ProcessControl " +
                      "WHERE [EffectiveDate] = @EffectiveDate";

        using (SqlConnection oConn = GetNewConnection())
        {
            using (SqlCommand cmd = new SqlCommand(sSQL, oConn))
            {
                cmd.Parameters.AddWithValue("@EffectiveDate", DBNull.Value);

                object resultObj = cmd.ExecuteScalar();
            }
        }
    }

I also tried:

cmd.Parameters.Add("@EffectiveDate", SqlDbType.DateTime).Value = DBNull.Value;

and

cmd.Parameters.AddWithValue("@EffectiveDate", SqlDateTime.Null);

The only time I get a result is if I use IS NULL. This is a simplified example, I will be optionally setting a value or Null for @EffectiveDate.

I have read other online information and it seems that all I need is a nullable database column and to use DBNull.Value for the parameter and it should work. What am I missing?

Looks like you're using raw ADO.NET. Have you considered switching to something that makes ADO.NET easier to work with, such as [Dapper](https://github.com/StackExchange/Dapper)? — mason, Oct 03 '18 at 14:55

score 2 · Answer 1 · answered Oct 03 '18 at 14:57

2

Because NULL in DB is unlike c#.

NULL = NULL returns false in Sql. You have to use IS NULL to compare

answered Oct 03 '18 at 14:57

Steve

11,696
7
43
81

score 2 · Accepted Answer · answered Oct 03 '18 at 15:14

Problem is unrelated to parametrization, but to the fact that the parameter can have a NULL value. Like what Steve said before you can't compare NULL to NULL directly or it won't return anything (it always evaluate to false according to the SQL standard).

The direct comparison in your example 2 works well when the parameter is guarranted to never be NULL, the example 1 is fine if it will always be NULL. However, if there are chances of it being NULL or not according to some external condition, we must consider that into the SQL query as well. Something like this will do:

SELECT [FileType]
FROM DTCC_APP_ProcessControl
WHERE [EffectiveDate] = @EffectiveDate OR @EffectiveDate IS NULL

This takes care of both situations, however, note that this expression is not SARGable and will not use an index on the EffectiveDate columns if there is one.

An alternative approach is to, according to the final value of the parameter, the client chooses to submit one query or another, chosing between the example 1 or 2 dynamically. This probably result in better performance and index usage.

It turns out every example I saw where parameters were used with DBNull.Value were Insert statements. I hadn't noticed that before. I thought DBNull.Value would result in "IS NULL" in the final query. Thanks for the explanation. — sldorman, Oct 03 '18 at 15:40
@sldorman There is no such problem with `INSERT`s because they're not used for comparison, that's the root of the problem, comparing a column to `NULL`, it doesn't even matters if it's a parameter or not. All that a parameter does is separating the data from the query itself, but it doesn't accounts for anything else, the `IS NULL` is up to you if you want it (and you really want it here). — Alejandro, Oct 03 '18 at 15:50

Why does IS NULL work but SQL Parameter with DBNull.Value for DateTime does not?

2 Answers2