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?

Answer 1

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.