CODEWITHSUNDEEP
c#asp.netstored-proceduressql-server-2008-r2
Subha
Subha

Reputation: 47

Send null value into stored procedure

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

Upvotes: 3

Views: 24040

Answers (4)

Hareendra Chamara Philips
Hareendra Chamara Philips

Reputation: 689

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. :)

Upvotes: 3

Chirag Soni
Chirag Soni

Reputation: 437

Use DBNull.Value to pass NULL.

Upvotes: 0

शेखर
शेखर

Reputation: 17614

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?

Upvotes: 5

rs.
rs.

Reputation: 27427

You can add null as default paramter value and dont send any value if you want it to be null

ex:

create procedure sp_name(@param1 varhcra(10), @param2 varchar(10)=null)

C#

command.Parameters.AddWithValue("@param1", "value1");
//dont pass any value for param2, it is considered null

Upvotes: -1

Related Questions