pass null parameter to SQL server query

Question

How can I pass a null parameter to a SQL server query. I have a simple table with a nullable int column. If I pass a .NET null value, I get a sql error. If I pass DBNull.Value, no row matches the filter. Is there a simple way to do this without using ISNULL.

    OleDbConnection connection = new OleDbConnection();
    connection.ConnectionString = ...;
    connection.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = connection;
    cmd.CommandText = "select * from myTable where myColumn = ?";
    OleDbParameter parameter = cmd.Parameters.Add(null, OleDbType.Integer);
    parameter.DbType = System.Data.DbType.Int32 ;
    parameter.IsNullable = true;
    parameter.Value = DBNull.Value; // no row returned
    parameter.Value = null; // sql error
    var reader = cmd.ExecuteReader();
...

Answer 1

Since NULL does not match anything (even NULL = NULL is false), you have no choice other than using the IS NULL statement.

Answer 2

In SQL, null acts a bit differently to other values - you can't just evaluate things as being = null, as this doesn't really work, you need to use "myColumn is null" instead.

In your case, when you need to match either a value or a null, you might need to use a case statement in your where clause.

A bit of reading: wikipedia

Answer 3

As ocdecio mention, NULL is not equal to itself. But you do have another option. If you are worried about NULL in this scenario, you can just put an empty string in your parameter value and write the query itself like this:

select * from myTable where COALESCE(myColumn,'') = ?