SqlCommand.Parameters.AddWithValue issue: Procedure or function X expects parameter @Y, which was not supplied

Question

I have a problem with the folowwing piece of code. I am passing a parameter (List<SqlParameter>) to a method executing the following code.

When it executes SQL Server throws an error saying that the proc expects a parameter that was not provided. I know this error and understand it, and when stepping through the code I can see that the cmdExecuteReader object has a collection of parameters with the correct name and value. What could be the problem?

     public SqlDataReader ExecuteReader(string storedProcedure, List<SqlParameter> parameters = null)
        {
                    SqlCommand cmdExecuteReader = new SqlCommand()
                    {
                        CommandType = System.Data.CommandType.Text,
                        Connection = conn,
                        CommandText = storedProcedure
                    };

                    if (parameters != null)
                    {
                        foreach (SqlParameter param in parameters)
                        {
                            cmdExecuteReader.Parameters.AddWithValue(param.ParameterName, param.Value);
                        }
                    }

                    if (conn.State == System.Data.ConnectionState.Closed)
                        conn.Open();
                    return cmdExecuteReader.ExecuteReader();
       }

Answer 1

Is the .Value set to null for any of the parameters? If so, they aren't sent. Try:

cmdExecuteReader.Parameters.AddWithValue(param.ParameterName,
        param.Value ?? DBNull.Value);

(note the null-coalescing with DBNull.Value)

Also, note that AddWithValue may impact your query-plan re-use, as (for strings etc) it uses the length of the value. If you need maximum performance it is better to setup the parameter manually with the defined sizes.

Also note that potentially some of the parameters in the incoming list could be input-output, output or result. I would be very tempted to substitute for something more like:

SqlParameter newParam = cmdExecuteReader.Parameters.Add(
      param.ParameterName, param.SqlDbType, param.Size);
newParam.Value = param.Value ?? DBNull.Value;
newParam.Direction = param.Direction;