CODEWITHSUNDEEP
sql-serverstored-procedures
Samantha J T Star
Samantha J T Star

Reputation: 32848

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

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 ?

Upvotes: 1

Views: 1253

Answers (3)

Steve
Steve

Reputation: 216363

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

Upvotes: 4

Dan Guzman
Dan Guzman

Reputation: 46415

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));

Upvotes: 2

Solomon Rutzky
Solomon Rutzky

Reputation: 48924

Either use DbNull.Value or SqlInt32.Null.

Upvotes: 2

Related Questions