Stored procedure expects parameter that is being passed

Question

From the following code I get the error that @StartDate is not supplied, however stepping through the two date range parameters have a valid value:

SqlParameter[] ps = new SqlParameter[]
{
    new SqlParameter("@StartDate", startDate),
    new SqlParameter("@EndDate", endDate)
};

List<AttendanceReportViewModel> res = db.Database.SqlQuery<AttendanceReportViewModel>(Local.queries["AttendanceReport"], ps).ToList();

return res;

The stored procedure :

ALTER PROCEDURE [dbo].[GetAttendanceReport]
    @StartDate datetime,
    @EndDate datetime
AS
BEGIN
    SET NOCOUNT ON;

    exec [REMOTE_SRV].LWD.dbo.ReportAttendance_sp @StartDate = @StartDate, @EndDate = @EndDate;
END

Everything works fine when I execute the stored procedure in SQL Server Management Studio but doesn't from within the application.

possible duplicate of How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5 — Johan Buret

Aaron Palmer Aaron Palmer · Accepted Answer · 2014-01-29T13:53:19

If your Local.queries["AttendanceReport"] looks something like this:

Local.queries["AttendanceReport"] = "yourProc @StartDate, @EndDate"

Then try this:

List<AttendanceReportViewModel> res = db.Database.SqlQuery<AttendanceReportViewModel(
    Local.queries["AttendanceReport"], 
    new SqlParameter("StartDate", startDate),
    new SqlParameter("EndDate", endDate)
).ToList();

Stored procedure expects parameter that is being passed

1 Answers