How do I pass a null value into a parameter for a SqlCommand

Question

Before anyone comments that this has been answered before in other question I KNOW that..but in spite of the answers I have reviewed at

• Assign null to a SqlParameter
• Sending null parameters to Sql Server
• Best method of assigning NULL value to SqlParameter

and even my own question at

• SQLCommand.CreateParameter.Value = DBNull.Value not returning results

I am unable to get my query to return values with null parameters

I have tried simplifying my code so it can be seen here.

I have also tried this with

 int? i = null;

 SqlConnection connection = new SqlConnection(Properties.Settings.Default.connstring.ToString());

 SqlCommand cmd = new SqlCommand();
 cmd.Connection = connection;
 cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk";
 cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull);

 cmd.Connection.Open();

 var dataReader = cmd.ExecuteReader();
 var dataTable = new DataTable();
 dataTable.Load(dataReader);

 cmd.Connection.Close();

I have tried variations on this where I just did

cmd.Parameters.AddWithValue("@parent_pk", DBNull.Value);

And I have tried using the query

cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk or @parent_pk Is Null";

I tried explicitly declaring the parameter as nullable

cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull).IsNullable = true;

Which for some reason I thought I had working hence my reason for accepting the answer I did but I was mistaken that just returns everything to me no matter what the value.

I know the command object is connecting and returns data because if I put in a valid value (say 27) it returns the record...I also know that there are records with Null as the value...but no matter how I try to set it up I keep getting nothing returns when I try to pass a null value as the parameter.

Anyone who can help me figure out what I'm doing wrong here I would be grateful.

Answer 1

Since my solution from comment worked - will post it here. Basically the problem as already described (and even already answered in your previous question) is that you need to use IS NULL to compare values with null in sql. Since you can have two cases (your parameter is either null or not) - you have to test for both conditions like this:

where parent_pk = @parent_pk or (@parent_pk IS NULL and parent_pk IS NULL)

Answer 2

The problem is in your choice of SQL queries:

cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk";

if @parent_pk has a value of NULL it will return nothing since even if parent_pk is null in SQL Server NULL = NULL return false. NULL is NULL return true.

cmd.CommandText = "Select * from view_nests where parent_pk = @parent_pk or @parent_pk Is Null";

This query returns everything because if you pass through a NULL for @parent_pk then the clause @parent_pk is NULL will evaluate true since @parent_pk doesn't change per row evaluated.

To accomplish what you're trying to do you would need to do something like:

string nullCommandText = "Select * from view_nests where parent_pk IS @parent_pk";
string commandText = "Select * from view_nests where parent_pk = @parent_pk";
cmd.CommandText = i == null? nullCommandText : commandText;
cmd.Parameters.AddWithValue("@parent_pk", i ?? Convert.DBNull);

Two other comments:

1. Shouldn't use *, list the columns you want.
2. If you have to do this kind of logic, create a stored procedure on the db side and use that instead.