Entity Framework calling stored procedure expects parameter which was not supplied

Question

I am calling my SP via Entity Framework like this :

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
      "super_group @user, @orderbyUnique",
      new SqlParameter("@user", userName),
      new SqlParameter("@orderbyUnique", true)).First();

And getting the error

Procedure or function 'super_group' expects parameter '@orderbyUnique', which was not supplied.

As you can see above I am supplying it.

Here's the stored procedure:

ALTER PROCEDURE [dbo].[super_group]
     @user nvarchar(30)
    ,@stepLockDelay varchar(10) = '00:00:00'
    ,@orderbyUnique bit
AS

Any ideas why I am getting this error ?

Answer 1

It probably should complain about the @user parameter as well if this is the case, but anyway - try to supply the parameter without the @ prefix:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
    "super_group @user, @orderbyUnique",
    new SqlParameter("user", userName),
    new SqlParameter("orderbyUnique", true)
).First();

Answer 2

The issue is the SQL that EF is producing for NULL values must not be compatible with our actual Sql Server. I'm using EntityFramework 6, but I don't think the implementation has changed since 4.3.

When I turned on tracing I get the following output after executing similar code to yours above:

exec sp_executesql N'super_group',N'@userName nvarchar(4000)',@userName=default

The issue lies in the "default" value passed instead of "NULL" and the error we see comes from SQL server. If you want a quick fix and don't need named parameters you can just use this:

NextSuperGroup supergroup = entities.Database.SqlQuery<NextSuperGroup>(
   "super_group",
    userName).First();

Which produces something like this and works nicely:

exec sp_executesql N'super_group',N'@p0 nvarchar(4000)',@p0=NULL

For named parameters, you need to use the sql parameter and set the value or SqlValue property explicitly to DBNull.Value (Crazy I know). Something like this:

var parameter = new SqlParameter("userName", SqlDbType.VarChar);
parameter.SqlValue = username.SqlNullIfEmpty(); // Implemented with an extension method

Hope that helps.

Answer 3

Parameter not passed issue when multiple parameter passed

I was missing 'SPACE' after the first parameter's 'COMMA'

var parameter = new List<object>();
var param = new SqlParameter("@CategoryID", CategoryID);
parameter.Add(param);
param = new SqlParameter("@SubCategoryID", SubCategoryID);
parameter.Add(param);

List<tbl_Product> QueryResult = db.Database.SqlQuery<tbl_Product>("EXEC SP_GetProducts @CategoryID, @SubCategoryID ", parameter.ToArray()).ToList();

Answer 4

I my case I fixed the issue by explicitly adding the type to parameter I was passing in which was defined in the stored procedure as a bit

        var paramExcludeExported = new SqlParameter
        {
            ParameterName = "ExcludeExported",
            SqlDbType = SqlDbType.Bit,
            Value = 0
        };