CODEWITHSUNDEEP
c#sqlsql-server
Jack Thor
Jack Thor

Reputation: 1594

Use Null as parameter in SQL

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.

Upvotes: 0

Views: 895

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

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.

Upvotes: 0

Brendan Hill
Brendan Hill

Reputation: 3732

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.

Upvotes: 1

Martin Cron
Martin Cron

Reputation: 1242

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.

Upvotes: 0

Related Questions