CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkstored-procedures
Johnathon64
Johnathon64

Reputation: 1320

EF6 stored procedure must declare scalar variable

I am trying to call a stored procedure using C# EF6 to bring back data. I have tried to run the stored procedure in SQL management studio and it seems to work fine, however when I try to run it in my application I get an error saying "Must declare the scalar variable "@devID"

Here is part of my method in my application calling the stored procedure

 public IHttpActionResult GetMetrics(int deviceID, string attribute, string startDate)
    {

        if (deviceID == 0)
        {
            return NotFound();
        }

        var metrics = db.Database.SqlQuery<Metrics>("GetMetrics @devID, @MetricType, @startTime", deviceID, attribute, startDate).ToList();

and here is my stored procedure:

    ALTER PROCEDURE [dbo].[GetMetrics]
    -- Add the parameters for the stored procedure here
    @devID int,
    @MetricType nvarchar(20),
    @startTime nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * 
    FROM dbMetrics
    WHERE deviceID = @devID and MetricType = @MetricType and timeStamp >= @startTime
    ORDER BY timeStamp 
END

Upvotes: 3

Views: 3073

Answers (1)

Jamie Dunstan
Jamie Dunstan

Reputation: 3765

As per the documentation, if you want to use named parameters, you need to pass SqlParameter objects like this:

var metrics = db.Database.SqlQuery<Metrics>("GetMetrics @devID, @MetricType, @startTime", 
    new SqlParameter("devID", deviceID),
    new SqlParameter("MetricType", attribute),
    new SqlParameter("startTime", startDate)
).ToList();

Upvotes: 7

Related Questions