17

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?

James123
  • 11,184
  • 66
  • 189
  • 343
  • http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – Ric Dec 04 '13 at 21:22

2 Answers2

35

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>();
Ant P
  • 24,820
  • 5
  • 68
  • 105
  • You are awesome, Microsoft are not, see here where they claim you can simply add the parameters https://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: https://stackoverflow.com/questions/22705767/sqlparametercollection-only-accepts-non-null-sqlparameter-type-objects-not-str – Kinetic May 03 '22 at 14:18
1

Try

var results = _MiscContext.Database.SqlQuery<int>(
              "exec sp_GetStaff {0}",
              customerNumber).ToList();
Arturo Martinez
  • 3,737
  • 1
  • 22
  • 35