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(sql, params) with stored procedure? EF Code First CTP5](http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced) — Johan Buret, Jan 29 '14 at 13:47
Just to remove a stupid possibility... is SSMS and the Application both talking to the exact same database? — freefaller, Jan 29 '14 at 13:50
`@StartDate = @StartDate` you prob. should consider renaming the parameter what you accept. — SQLMason, Jan 29 '14 at 13:51
@freefaller the SP above calls another on a linked server. I've checked all params and they have a value and are of type `datetime` — Lee, Jan 29 '14 at 13:53
@lee what are the values that you're passing? DateTime.Min is not the same in .NET and SQL Server. — SQLMason, Jan 29 '14 at 13:54
I'm passing .NET `DateTime` vars as params to a local SP that accepts `DATETIME`. I've not had this data type problem before, so I expect they're compatible. — Lee, Jan 29 '14 at 13:58

score 1 · Accepted Answer · answered Jan 29 '14 at 13:53

1

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();

answered Jan 29 '14 at 13:53

Aaron Palmer

8,722
9
47
76

This works, thank you. I was also missing a comma between my params. – Lee Jan 29 '14 at 13:57

Stored procedure expects parameter that is being passed

1 Answers1