Use Null as parameter in SQL

Question

I have this column in my table that is nullable, and when I execute a store procedure that does an insert via SQL Server Manager and I have a null value for the parameter for that column it does not complain and works

EXEC @return_value = my_store_proc
 @my_last_name = NULL, ...

but in my C# code where I am building the parameter it complains

System.Data.SqlClient.SqlException : Procedure or function 'my_store_proc' expects 
parameter ' @my_last_name', which was not supplied.

here is my C# code

command.Parameters.AddWithValue("@my_last_name ", null);

but when I replace the null with an empty string it works perfectly fine. I am not sure why it does not like null here. Also I tried using DbNull.value in place of null, but can not seem to find System.DBNull when I try to add a reference so I am looking for other alternative.

"Also I tried using DbNull.value in place of null, but can not seem to find System.DBNull when I try to add a reference" DBNull is in mscorlib, what are you trying to add a reference to? — Preston Guillot
– Preston Guillot, Commented Nov 19, 2013 at 2:10

Brendan Hill · Accepted Answer · 2013-11-19 02:19:36Z

1

You must use System.DBNull.Value in this case. Otherwise, the parameter assumes you are not passing the parameter, rather than passing the null value.

System.DBNull is basically always available, if you can't find it then perhaps post the error message.

answered Nov 19, 2013 at 2:19

Brendan Hill

3,8124 gold badges38 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jack Thor Over a year ago

OMG, you can just slap me or something. I was using DbNull not DBNull that's why it was saying the value does not exist. I hate it when this ugh.

Brendan Hill Over a year ago

Haha, no problem, glad it's sorted out.

Martin Cron · Accepted Answer · 2013-11-19 02:09:32Z

0

You can change the signature of your stored procedure to make the parameter have a default null value, and then just not append that parameter to your command. ADO.NET isn't really smart enough to tell the difference between explicitly passing a null parameter value or just not passing a required parameter.

answered Nov 19, 2013 at 2:09

Martin Cron

1,2621 gold badge9 silver badges10 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2013-11-19 02:29:16Z

0

First of all, don't use the .AddWithValue() method. It makes .Net guess the type to use for the argument, and sometimes .Net will get it wrong. Usually when that happens the code will still work, but it forces Sql Server to do a little extra work to perform an extra conversion. Sometimes when that happens, it will break Sql Server's ability to use an index, which can result very large performance penalties. Use one of the .Add() overloads that has you provide a specific type instead.

So you can write the code like this:

//The "50" here is a guess. Replace it with the actual column length
command.Parameters.Add("@my_last_name ", SqlDbType.NVarChar, 50).Value = DBNull.Value;

If you have a lot of parameters where several can be null, I like to do this:

command.Parameters.Add("@my_last_name ", SqlDbType.NVarChar, 50).Value = someVariableThatCouldBeNull;
// Other parameter
// Third Parameter
// fourth
// etc

foreach (var p in command.Parameters.Where(p => p.Value == null))
{ p.Value = DBNull.Value;}

DBNull is part of the System namespace, and it's included in the same assembly that provides core types like int and string, so there shouldn't be anything extra needed to use it.

edited Nov 19, 2013 at 2:29

answered Nov 19, 2013 at 2:23

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

1 Comment

Jack Thor Over a year ago

I like your hint on handling multiple null

Collectives™ on Stack Overflow

Use Null as parameter in SQL

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related