How to pass a null variable to a SQL Server stored procedure from C# / .NET code

Question

I'm calling a SQL Server stored procedure from a piece of C# / .NET code like this:

SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters);

where the sqlParameters variable is defined as:

SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS];

Log.Logger.Debug(string.Format("Running proc: {0} ", STORED_PROC_NAME));

SqlParameters[0] = new SqlParameter("fieldID", SqlDbType.BigInt );
SqlParameters[0].Value = fieldID;
SqlParameters[0].Direction = ParameterDirection.Input;

I need to now pass in another two parameters to this stored procedure, both are of type SqlDateTime, which are going to be NULL in this case.

Justin Niessner · Accepted Answer · 2009-07-30 15:39:24Z

95

SqlParameters[1] = new SqlParameter("Date1", SqlDbType.SqlDateTime);
SqlParameters[1].Value = DBNull.Value;
SqlParameters[1].Direction = ParameterDirection.Input;

...then copy for the second.

answered Jul 30, 2009 at 15:39

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

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

Comments

Dan Diplo · Accepted Answer · 2009-07-30 15:40:03Z

30

Use DBNull.Value Better still, make your stored procedure parameters have defaults of NULL. Or use a Nullable<DateTime> parameter if the parameter will sometimes be a valid DateTime object

answered Jul 30, 2009 at 15:40

Dan Diplo

25.4k4 gold badges71 silver badges90 bronze badges

Comments

TylerH · Accepted Answer · 2025-08-20 19:51:06Z

15

You can pass the DBNull.Value into the parameter's .Value property:

SqlParameters[0] = new SqlParameter("LedgerID", SqlDbType.BigInt );
SqlParameters[0].Value = DBNull.Value;

Just adjust for your two DateTime parameters, obviously - just showing how to use the DBNull.Value property value here.

edited Aug 20 at 19:51

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jul 30, 2009 at 15:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

TylerH · Accepted Answer · 2025-08-19 20:57:56Z

14

Here's a fairly clean way to create a nullable parameter:

new SqlParameter("@note", (object) request.Body ?? DBNull.Value);

If request.Body has a value, then its value is used. If it's null, then DbNull.Value is used.

edited Aug 19 at 20:57

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 26, 2017 at 22:10

Lavamantis

3,8262 gold badges27 silver badges22 bronze badges

Comments

TylerH · Accepted Answer · 2025-08-20 19:51:21Z

8

I use a method to convert to DBNull if it is null

// Converts to DBNull, if Null
public static object ToDBNull(object value)
{
    if (null != value)
        return value;
    return DBNull.Value;
}

So when setting the parameter, just call the function

 sqlComm.Parameters.Add(new SqlParameter("@NoteNo", LibraryHelper.ToDBNull(NoteNo)));

This will ensure any nulls, get changed to DBNull.Value, else it will stay the same.

edited Aug 20 at 19:51

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Nov 18, 2011 at 15:00

IAmGroot

13.9k18 gold badges87 silver badges156 bronze badges

3 Comments

Gayan Over a year ago

that's good solution i changed static method to extension method for objects

public static object ToDbNull(this object value)         {             if (null != value)                 return value;             return DBNull.Value;         }

C. Ridley Over a year ago

Except that a DateTime isn't a nullable type, so this won't work for DateTime (as specified in the question), or for any other non-nullable value types, unless you declare all you vars to be Nullable<T> which doesn't seem a particularly good approach.

saluce Over a year ago

It might be simpler, rathar than having this extension method, to simply use the null coalescing operator: new SqlParameter("@NoteNo", NoteNo as object ?? System.DbNull)

GoAntonio · Accepted Answer · 2015-07-31 08:46:36Z

2

try this! syntax less lines and even more compact! don't forget to add the properties you want to add with this approach!

cmd.Parameters.Add(new SqlParameter{SqlValue=(object)username??DBNull.Value,ParameterName="user" }  );

edited Jul 31, 2015 at 8:46

answered Jul 31, 2015 at 8:37

GoAntonio

3113 silver badges14 bronze badges

Comments

Mike Z · Accepted Answer · 2009-07-30 15:40:43Z

0

    SQLParam = cmd.Parameters.Add("@RetailerID", SqlDbType.Int, 4)
    If p_RetailerID.Length = 0 Or p_RetailerID = "0" Then
        SQLParam.Value = DBNull.Value
    Else
        SQLParam.Value = p_RetailerID
    End If

answered Jul 30, 2009 at 15:40

Mike Z

964 bronze badges

Comments

ablaze · Accepted Answer · 2015-08-03 15:30:20Z

0

Let's say the name of the parameter is "Id" in your SQL stored procedure, and the C# function you're using to call the database stored procedure is name of type int?. Given that, following might solve your issue :

public void storedProcedureName(Nullable<int> id, string name)
{
    var idParameter = id.HasValue ?
                new SqlParameter("Id", id) :
                new SqlParameter { ParameterName = "Id", SqlDbType = SqlDbType.Int, Value = DBNull.Value };

    // to be continued...

answered Aug 3, 2015 at 15:30

ablaze

7229 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to pass a null variable to a SQL Server stored procedure from C# / .NET code

8 Answers 8

Comments

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

Comments

Comments

3 Comments

Comments

Comments

Comments

Linked

Related