18

I am trying to execute stored procedure from bcontext.Database.SqlQuery using EF5.

It is throwing an error must declare the scalar variable '@custid'

  var results = _MiscContext.Database.SqlQuery<int>(
              "exec sp_GetStaff @custid",
              customerNumber).ToList<int>();

SP returns 1 if customerNumber is staff otherwise it return empty row.

ALTER PROCEDURE [dbo].[sp_GetStaff]
    @custid varchar(12)
AS
BEGIN

    SET NOCOUNT ON;
SELECT 
1 AS [C1]
FROM  [dbo].[Staff] with (nolock)
WHERE [CUSTOMER_ID] = @custid


END

How to manage this?

Improve this question
1

2 Answers 2

Reset to default
36

Since you're using named parameters, you have to specify the matching name for the parameter you're passing.

var results = _MiscContext.Database.SqlQuery<int>(
    "exec sp_GetStaff @custid",
    new SqlParameter("custid", customerNumber)).ToList<int>();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You are awesome, Microsoft are not, see here where they claim you can simply add the parameters learn.microsoft.com/en-us/ef/core/querying/raw-sql. Also, if you get a weird error when you try the above fix, check the namespaces used. See: stackoverflow.com/questions/22705767/…
1

Try

var results = _MiscContext.Database.SqlQuery<int>(
              "exec sp_GetStaff {0}",
              customerNumber).ToList();
Improve this answer

1 Comment

This loses the performance and security benefits of a parameterized query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.