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

sql

prompt-engineering

interview-preparation

best-practices

byAnton Shumikhin·Jan 9, 2025

Easily pass a NULL to a SQL Stored Procedure using DBNull.Value like this:

var cmd = new SqlCommand("proc", conn) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("@Param", yourVar ?? DBNull.Value);

If yourVar unravels to null, the winning champion, DBNull.Value steps in, conveying NULL to the SQL universe.

Common scenarios for different variable types

Here, we dissect handling nullable types in C# and pass a null value to a SQL Stored Procedure like a pro:

Wrestling with nullable reference types:

// Slide your nullable reference type like a smooth criminal.
cmd.Parameters.AddWithValue("@NullableString", nullableStringVar ?? DBNull.Value);

Slaying nullable value types like DateTime:

// This is one small step for value types, a giant leap for your code.
cmd.Parameters.AddWithValue("@NullableDateTime", nullableDateTimeVar.HasValue ? nullableDateTimeVar.Value : DBNull.Value);

Or embrace the art of shorthand:

// Why write a novel when a haiku will do?
cmd.Parameters.AddWithValue("@NullableDateTime", (object)nullableDateTimeVar ?? DBNull.Value);

Perfecting DBNull with a conversion method

Craft a killer ToDBNull function:

public static object ToDBNull(object value)
{
    // return value or the cool kid: DBNull.Value. Let the Hunger Games begin!
    return value ?? DBNull.Value;
}

Then, summon it like a mighty warrior:

// In DBNull.Value we trust!
cmd.Parameters.AddWithValue("@Param", ToDBNull(yourVar));

Mastering default NULLs in SQL Stored Procedures:

Set your SQL Stored Procedure parameters with DEFAULT NULL to be the hero:

CREATE PROCEDURE ProcName
    @Param DateTime = NULL
AS
BEGIN
    // Handling nulls like they are no big deal!
END

This lets you skip the nullable parameter if it's playing hide and seek:

if (yourVar != null)
{
    // Oh, a wild variable appeared! Let's catch it.
    cmd.Parameters.AddWithValue("@Param", yourVar);
}

Extra tips and tricks

The art of specifying parameter types

Writing software is not just about function. It is also about form. So, explicitly specify the parameter type, instead of leaning on AddWithValue. That's for amateurs. You're a pro!

// Here's how a Python star does it. So sleek, so clean!
cmd.Parameters.Add(new SqlParameter("@Param", SqlDbType.DateTime) { Value = ToDBNull(yourVar) });

Receiving output parameters living the NULL life:

var paramOutput = new SqlParameter("@OutputParam", SqlDbType.Int)
{
    // Fancy word for "give me the loot!"
    Direction = ParameterDirection.Output
};
cmd.Parameters.Add(paramOutput);

// Post command execution, grabs the output like a boss!
int? result = paramOutput.Value != DBNull.Value ? Convert.ToInt32(paramOutput.Value) : (int?)null;

Suiting up your SP to handle NULLs

// SQL's talent show is ready for "The Amazing NULL"
IF @Param IS NULL
    // SP rolls out the red carpet for NULL
ELSE
    // Proceed business as usual

Showcasing Nullable<T> in client-side logic

Using Nullable<DateTime> (or its hip alias DateTime?) in C#, makes nullable logic seem as simple as 1.2.3. Here's the proof:

// Magic trick: Nullable<DateTime> 
DateTime? nullableDate = GetDate(); // Returns null or a DateTime value
cmd.Parameters.AddWithValue("@Date", (object)nullableDate ?? DBNull.Value);