Entity Framework calling stored procedure expects parameter which was not supplied

c# entity-framework sql sql-server stored-procedures

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 ?

1
13
2/25/2014 12:32:28 PM

Accepted Answer

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();
14
2/25/2014 12:45:36 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow