How can I send a null value to a stored procedure using SqlParameter?

Question

I have the following stored procedure:

CREATE PROCEDURE dbo.sp_ins_test
   @Title          NVARCHAR (100) ,
   @ExamId         INT            ,
   @TopicId        INT                           
AS 
BEGIN
INSERT INTO dbo.Test
(
    TestId,
    ExamId,
    TopicId
)
VALUES (
    @TestId,
    @ExamId,
    @TopicId
)

Here's the code that calls the stored procedure:

List<SqlParameter> parameterList = new List<SqlParameter>();
parameterList.Add(new SqlParameter ("@Title", "Practice " + testNumber));
parameterList.Add(new SqlParameter ("@ExamId", examId == 0 ? null : examId));
parameterList.Add(new SqlParameter ("@TopicId",  testTopicId == 0 ? null : testTopicId));
SqlParameter[] parameters = parameterList.ToArray();
int result = db.Database.ExecuteSqlCommand(sql, parameters);

My intention was that if the examId or the topicId were 0 then I want to pass a null to the sp_ins_test stored procedure. However I get an error saying:

Error   2
Type of conditional expression cannot be determined because there is no implicit 
conversion between '<null>' and 'int'

for both the examId and testTopicId lines. Can anyone tell me how I can insert a null into these columns if the value of examId or testTopicId is 0 ?

score 4 · Accepted Answer · edited May 23 '17 at 11:53

As other answers here, you need to pass DBNull.Value, however I suggest to use this approach

SqlCommand cmd = new SqlCommand();
List<SqlParameter> parameterList = new List<SqlParameter>()
{
    new SqlParameter("@Title", SqlDbType.NVarChar) { Value = "Practice " + testNumber.ToString()},
    new SqlParameter("@ExamId", SqlDbType.Int) { Value = (examId == 0 ? DBNull.Value : (object)examId)},
    new SqlParameter("@TopicId", SqlDbType.Int) { Value = (testTopicId == 0 ? DBNull.Value : (object)testTopicId)},
};    
cmd.Parameters.AddRange(parameterList.ToArray());

Do not use the obsolete call that create a new parameter passing directly the value. It is ambiguous and could cause a problem (this is the reason that the AddWithValue method has been added)

See Why we need AddWithValue
Differences between Parameters.Add and Parameters.AddWithValue

score 2 · Answer 2 · answered Jul 19 '14 at 15:36

2

Either use DbNull.Value or SqlInt32.Null.

answered Jul 19 '14 at 15:36

Solomon Rutzky

46,688
9
128
171

Dan Guzman · Answer 3 · 2014-07-19T15:50:36.977

2

Specify the SqlType Null to pass NULLs:

        parameterList.Add(new SqlParameter("@ExamId", examId == 0 ? SqlInt32.Null : examId));
        parameterList.Add(new SqlParameter("@TopicId", testTopicId == 0 ? SqlInt32.Null : testTopicId));testTopicId));

edited Jul 19 '14 at 15:50

answered Jul 19 '14 at 15:36

Dan Guzman

43,250
3
46
71

You need to cast examId and testTopicId to object otherwise the conditional operator will raise an error – Steve Jul 19 '14 at 15:42
@Steve, thanks for pointing that out. I changed the code to use the SqlType Null as srutzky suggested. – Dan Guzman Jul 19 '14 at 15:52

How can I send a null value to a stored procedure using SqlParameter?

3 Answers3