.net: How to execute a stored procedure with a nullable parameter in C #?

Question

How to execute a stored procedure with a nullable parameter in C #?

EDIT

Actually, I wrote the code below. As you can see, the status parameter is a value type with a zero value. Is it correct? or not?

public void LoadAll(DataTable tb, int? status=null)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = this.connectionString;

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "USP_OrganizationChartSelect";
                    SqlCommandBuilder.DeriveParameters(command);
                    command.Parameters["@Status"].Value = status.HasValue ? status : null;
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    tb.Clear();
                    adapter.Fill(tb);
                    adapter.Dispose();
                    adapter = null;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

thank

Answer 1

You can try DBNull.Valueinstead nullor omit the parameter when the nullable value is not the whole value.

Answer 2

If the parameter is null, it must be a value type. Suppose you have an int nullable parameter, then you can pass it that way.

  int? nullableParam = null;
  nullableParam = 10; //set the value if any
  //Pass nullableParam to your sp call.

Answer 3

nullable sp, :

select * from table
where (code = @code or @code is null)

, @code null, .