Entity Framework SQL Parameters not getting supplied

Question

This thread gives a number of ways to provide a parameter to a stored procedure.

None of them are working for me.

This works:

this.PayrollContext.Database.SqlQuery<CSRRateEntity>("ord_getCSRRate @csr_num = '4745', @ord_pay_period_id = 784").ToList();

This does not:

return this.PayrollContext.Database.SqlQuery<CSRRateEntity>("Exec ord_getCSRRate @csr_num, @ord_pay_period_id",
            new SqlParameter("csr_num", "4745"),
            new SqlParameter("ord_pay_period_id", 784)
            ).ToList();

The Error message is that parameter is not supplied.

Have tried all the variations I can think of and still get that same error message.

This is using Code First, so no import is required. The SP is found, it is just missing the parameters.

score 0 · Answer 1 · edited Mar 16 '15 at 21:41

Did you import the stored procedure into the edmx?

In the implementation that we have for the stored procedure it creates parameters like this:

    var inputIdParameter = inputId.HasValue ?
        new ObjectParameter("InputId", inputId) :
        new ObjectParameter("InputId", typeof(int));

The result of the stored procedure function is given like this

return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<T>("[SP_NAME]",  inputIdParameter);

score 0 · Accepted Answer · answered Mar 17 '15 at 15:01

The solution turned out to be to change the order of the parameters in the stored procedure so that optional parameters come after the required parameters (like in c#).

Works:

ALTER procedure [dbo].[ord_GetCSRRate]@ord_pay_period_id int,@csr_num varchar(10) = null, @csr_id int = null, @update_csr_pay_flag bit = 0

Does not work:

ALTER procedure [dbo].[ord_GetCSRRate]@csr_num varchar(10) = null, @ord_pay_period_id int, @csr_id int = null, @update_csr_pay_flag bit = 0, @recursion_flag bit = 0

Entity Framework SQL Parameters not getting supplied

2 Answers2