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 ?

Community · Accepted Answer · 2017-05-23 11:53:55Z

4

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

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jul 19, 2014 at 15:51

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Solomon Rutzky · Accepted Answer · 2014-07-19 15:36:29Z

2

Either use DbNull.Value or SqlInt32.Null.

answered Jul 19, 2014 at 15:36

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Comments

Dan Guzman · Accepted Answer · 2014-07-19 15:50:36Z

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, 2014 at 15:50

answered Jul 19, 2014 at 15:36

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

2 Comments

Steve Over a year ago

You need to cast examId and testTopicId to object otherwise the conditional operator will raise an error

Dan Guzman Over a year ago

@Steve, thanks for pointing that out. I changed the code to use the SqlType Null as srutzky suggested.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related