How to pass a null variable to a SQL Stored Procedure from C#.net code

Im calling a SQL stored procedure from a piece of C#.net code:

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

where the sqlParameters variable is defined as:

  • How to get the nth Highest mark per branch?
  • Can someone help me simplify/speed up this Function?
  • Timeout not being honoured in connection string
  • Modify binary(1) column in SSMS grid
  • Return a single row if no rows are found
  • How to run 2008R2 SSIS packages on SQL Server 2014 Standard environment?
  •         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 Proc, (both are of type SqlDateTime), which are going to NULL in this case.

    Thanks,

    IN

  • Could I possibly be shown how to use a procedure to add data to 2 separate tables, then reference one of them in the other
  • how to format bit datatype column in datagridview c#?
  • SQL: programmatically copy stored procedures from one db to another
  • SQL replace all NULLs
  • “Index was outside the bounds of the array” while Compare SQL schema by SQL compare tool
  • SQL inline if statement type question
  • 8 Solutions collect form web for “How to pass a null variable to a SQL Stored Procedure from C#.net code”

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

    …then copy for the second.

    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

    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.

    Marc

    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.

    Old question, but 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 it’s value is used. If it’s null, then DbNull.Value is used.

        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
    

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

    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...
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.