Send null value into stored procedure

Question

How to send null value for table value parameters to a stored procedure in C#?

Answer 1

Use like this

command.Parameters.AddWithValue("@param", DBNull.Value);

Here is a good link on the same topic
How to pass a null variable to a SQL Stored Procedure from C#.net code

You can go through link.
Using DBNull.Value with SqlParameter without knowing sqlDbType?

Answer 2

There is an little issue using DBNull.Value though. Lets say i have a bit in the store procedure named IO.

in the store procedure

   CREATE PROCEDURE [dbo].[stp_Check]
   @IO BIT

Then at the code I add the parameter as follows.

   object parameter = new SqlParameter("IO", true); //using false is the same.

And then I call the store procedure

   _repositary.DataContext.Database.SqlQuery<CallDetail>("exec stp_Check @IO", parameter)();

Here _repository is my DataRepository class with context. Knowing that it'll execute the sp is enough for this.

You can catch the call using sql server profiler. That will be generated as follows.

declare @p24 int
set @p24=7
exec sp_executesql N'exec stp_Check @IO', --parameter passed
N'@IO bit', -- parameter with the type passed
@IO=1 --parameter value used when executing the sp
select @p24

The problem is this. When you create the parameter with DBNull.Value as follows.

    object parameter = new SqlParameter("IO", (object)DBNull.Value);

From the sql server profiler what you can find out is

declare @p24 int
set @p24=7
exec sp_executesql N'exec stp_Check @IO', --parameter passed
N'@IO nvarchar(4000)', -- the passes type is not boolean
@IO=NULL --parameter value is NULL
select @p24

True that this will work.But this is not what is intended. I prefer

using System.Data.SqlTypes;

You can pass null as follows.

 object parameter = new SqlParameter("IO", System.Data.SqlTypes.SqlBoolean.Null );

Then the request is like this.

declare @p24 int
set @p24=7
exec sp_executesql N'exec stp_Check @IO', --parameter passed
N'@IO bit', -- the passes type is boolean
@IO=NULL --parameter value is NULL
select @p24

There are null values for all sqltypes such as int, bigint, datetime etc. Hope this help. Cheers. :)