Call Stored Procedure with a null parameter value

Question

i've a stored procedure on sqlserver 2008 and one of the parameters accept null values. i don't know how to call that SP with a null value on the parameter. for a little more context i'm using EntityFramework 6xx

On the next example the parameters "@status, @Compatible" can have null as value, but i'm just getting an exception saying that those prarams was not provided.

public override IList<MyOutputType> SearchStuff(string Term, int? status, bool? Compatible, long TMPLDMID, int RangeFrom, int RangeTo)
{
    List<SqlParameter> par = new List<SqlParameter>();
    par.Add(new SqlParameter("@Term", System.Data.SqlDbType.VarChar, 50) { Value = "%" + Term + "%" });
    par.Add(new SqlParameter("@status", System.Data.SqlDbType.Int) { Value = status, IsNullable = true });
    par.Add(new SqlParameter("@Compatible", Compatible) { IsNullable = true });
    par.Add(new SqlParameter("@TMPLDMID", TMPLDMID));
    par.Add(new SqlParameter("@RangeFrom", RangeFrom));
    par.Add(new SqlParameter("@RangeTo", RangeTo));

    return db.Database.SqlQuery<MyOutputType>(
        "EXEC [spSearchForStuff] @Term, @status, @Compatible, @TMPLDMID, @RangeFrom, @RangeTo", par.ToArray()
    ).ToList();

Answer 1

I ran into the same issue. If the values were null it would load default. Which I actually needed a null, so for status you should be able to do below.

par.Add(new SqlParameter("@status", (object)status??DBNull.Value);

check this answer for more in depth examples https://stackoverflow.com/a/4556007/1248536