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.

Midtown

when you use SQL Profiler to look at what is actually being sent to SQL Server, what value is being passed instead of Null ? – Sirotek

Is there a reason for you not moving to stored procedure but writing plain queries inside Visual Studio? I also vote for @PhillipH's idea on SQL Profiler. – Wyly

If I understand correctly, what you need is: "parent_pk = @parent_pk or (@parent_pk IS NULL AND parent_pk IS NULL)" – Hanford

EVK your solution worked perfectly – Midtown

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)

Hanford

I am asking me why i didn't knew this already. Are there any alternatives to this approach? – Alexandrine

@Alexandrine You can use "set ansi_nulls off" to change how equality operators work with null values, wont recommend that though. By ISO sql standard, comparision of two null values is undefined (null = null is neither true, nor false), and there is a reason why is it so, so you better just live with this :) – Hanford

Oh, ok, thx.. But maybe you could also explain the reason why null == null ? should not be considered true, please.. – Alexandrine

I just could see that my database in fact is configured with SET ANSI_NULLS OFF and also with SET ANSI_NULL_DEFAULT OFF. I am wondering why it doesn't work if i set "... parms[0].Value = DBNull.Value;" (where parms[0] is a "new SqlParameter("@name", SqlDbType.VarChar, 50);") and then i let my Sql statement something like: ".. WHERE name = @name;". Why this doesn't work? Is it because generating the appropriate resulting SQL statement would be too "difficult" for the framework (because of the "... WHERE @name IS (NOT) NULL;" which should be generated there)?? – Alexandrine

Well hard to say why ansi nulls does not work in your case. As for sql, framework has nothing to do with it. If you do the same in pure sql (with parameters) - result is the same too. – Hanford

As for why, try read thia question and all ( not just accepted) answers. https://mcmap.net/q/121709/-why-does-null-null-evaluate-to-false-in-sql-server/5311735. Comments are too short to explain it, but eventually you will realize. – Hanford

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.

Shaughn