Sending null Parameter into stored procedure

Question

I have Subject class. It's members are id_subject (int) and subjectName (string)

Them I create a list

List<Subject> listSubject = new List<Subject>();

and send it to this stored procedure:

 public bool AddNewNews(News news, List<Subject> subject)
 {
        SqlParameter[] parameters = new SqlParameter[]
        {
           new SqlParameter ("@title",news.Title),
           new SqlParameter ("@text",news.Text),
           new SqlParameter ("@year",news.Year),    
           new SqlParameter ("@month",news.Month) ,    
           new SqlParameter ("@day",news.Day) ,   
           new SqlParameter ("@id_writer",news.Id_writer)  ,
           new SqlParameter ("@id_subject1",subject[0]!=null? subject[0].Id_subject:null),
           new SqlParameter ("@id_subject2",subject[1]!=null? subject[1].Id_subject:null) ,
           new SqlParameter ("@id_subject3",subject[2]!=null? subject[2].Id_subject:null), 
           new SqlParameter ("@id_subject4",subject[3]!=null? subject[3].Id_subject:null),
           new SqlParameter ("@id_subject5",subject[4]!=null? subject[4].Id_subject:null)
    };

    return SqlDBHelper.ExecuteNonQuery("AddNewNews", CommandType.StoredProcedure, parameters);
}

First: short if is not true

I cast them to object and send to the stored procedure

BEGIN TRANSACTION;

   DECLARE @last_id_news int

   BEGIN TRY
      insert into news (title, text, year, month, day, id_writer)
      values(@title, @text, @year, @month, @day, @id_writer)

      set @last_id_news = SCOPE_IDENTITY()

      if(@id_subject1 <> null)
      begin
           insert into news_subject (id_news, id_subject) 
           values (@last_id_news, @id_subject1)
      end

      if(@id_subject2 <> null)
      begin
         insert into news_subject (id_news, id_subject) 
         values (@last_id_news, @id_subject2)
      end

      if(@id_subject3 <> null)
      begin
         insert into news_subject (id_news, id_subject) 
         values (@last_id_news, @id_subject3)
      end

if(@id_subject4<>null)
begin
    insert into news_subject (id_news,id_subject) values (@last_id_news,@id_subject4)
end
if(@id_subject5<>null)
begin
    insert into news_subject (id_news,id_subject) values (@last_id_news,@id_subject5)
end

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

ROLLBACK TRANSACTION;
END CATCH;

RETURN

Why does it not work?

Use `DBNull.Value` instead of `null` (inside C# code of course). — , Jul 11 '14 at 12:47
Could you please provide what does the SP return? Exception maybe? — Oğuz Sezer, Jul 11 '14 at 12:48
You cannot use the usual equality or inequality operators for `NULL`. This will **NOT** work: `if(@id_subject4<>null)`. Instead, you need to check for `IS NULL` or `IS NOT NULL` - so replace all those checks with something like: `IF (@id_subject4 IS NOT NULL) .....` — marc_s, Jul 11 '14 at 12:50
possible duplicate of [How to assign null to a sqlparameter?](http://stackoverflow.com/questions/4555935/how-to-assign-null-to-a-sqlparameter) — Daniel Kelley, Jul 11 '14 at 13:08
@nimaSadeghpour dammit, I didn't spot the `.Id_subject` at the end of those lines - my mistake; this complicates things a bit - since we don't have the C# 6 null-preserving operator yet, you would have to use a conditional (ternary) operator, or similar - much like your existing code. In C# 6, this becomes possible to do much more cleanly. — Marc Gravell, Jul 11 '14 at 13:21

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

You are looking for DBNull.Value

Similar question on SO

edited May 23 '17 at 12:29

Community

1
1

answered Jul 11 '14 at 12:48

शेखर

17,412
13
61
117

i know it. but left side of short if is int and right side of is object or null – nimaSadeghpour Jul 11 '14 at 12:58
@nimaSadeghpour I demonstrate a cheeky `(object)` / `??` trick for that in my answer – Marc Gravell Jul 11 '14 at 13:00

score 1 · Answer 2 · answered Jul 11 '14 at 12:59

As already noted in comments and an answer, DBNull is the annoying requirement here; you can use the null-coalescing operator to make it fairly readable, though:

   new SqlParameter ("@id_subject1",((object)subject[0]) ?? DBNull.Value),
   new SqlParameter ("@id_subject2",((object)subject[1]) ?? DBNull.Value),
   new SqlParameter ("@id_subject3",((object)subject[2]) ?? DBNull.Value),
   new SqlParameter ("@id_subject4",((object)subject[3]) ?? DBNull.Value),
   new SqlParameter ("@id_subject5",((object)subject[4]) ?? DBNull.Value),

Or better: perhaps your ExecuteNonQuery method could loop over the parameters and check whether .Value == null, replacing it with DBNull.Value if it is.

Alternatively alternatively, consider a tool like "dapper" that will do it all for you conveniently:

connection.Execute("AddNewNews", new {
    title = news.Title,
    //...
    id_writer = news.Id_writer,
    id_subject1 = subject[0],
    //...
    id_subject5 = subject[4],
}, commandType: CommandType.StoredProcedure);

which is then fully parameterized with nulls handled appropriately.

i do it and send me this error.No mapping exists from object type Subject to a known managed provider native type. — nimaSadeghpour, Jul 11 '14 at 13:06

Sending null Parameter into stored procedure

2 Answers2