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"))
    using (var cmd = new SqlCommand())
        cmd.Connection = connection;
        cmd.CommandText = "Foo";
        cmd.CommandType = CommandType.StoredProcedure;
            new SqlParameter
                ParameterName = "@Name",
                SqlDbType = SqlDbType.NVarChar,
                Size = 255,
                Value = results.Reference

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