Monday, 11 November 2013

Adding a SqlParameter with a null reference - Procedure or function 'Foo' expects parameter '@Name', which was not supplied.

I was debugging a historical issue raised in some legacy code over a year ago.
The problem was reported that the database was raising an error under peak load whenever a payment service was unavailable.

The database error was:

 Procedure or function 'Foo' expects parameter '@Name', which was not supplied.

After some digging it transpires that the error was due to a little gotcha in the database access code.
The code was similar to:

using (var connection = new SqlConnection("Server=localhost;Database=TestDatabase;Integrated Security=SSPI"))
{
    connection.Open();
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = connection;
        cmd.CommandText = "Foo";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(
            new SqlParameter
            {
                ParameterName = "@Name",
                SqlDbType = SqlDbType.NVarChar,
                Size = 255,
                Value = results.Reference
            }
            );
        cmd.ExecuteNonQuery();
    }
}

If results.Reference is a .NET null reference, then the SqlParameter is NOT added. In this scenario if you want to add a NULL database value then you would need to modify the code to:


    Value = results.Reference ?? DbNull.Value;

No comments:

Post a Comment