Entity Framework .Net 6 with SQL Server stored procedure – parameter not supplied

Question

This is my first attempt at using EF to call a SQL Server stored procedure passing in parameters. This is an update stored procedure that I'm testing, and I keep getting an error

Procedure or function 'Employee_Update' expects parameter '@EmployeeId', which was not supplied

If I put a breakpoint in the code where it's calling the stored procedure, I can see the parameters and that they are populated properly. I've also copied and pasted the parameter names between the stored procedure and my code to make sure I didn't misspell something.

Everything I've read about and see in the code suggests that it should work. I've also confirmed the stored procedure code is good by executing the stored procedure directly from SQL Server Management Studio, it works just fine.

Anyone have a suggestion on what I can try or see something that looks like it wouldn’t work?

List<SqlParameter> sqlParms = new List<SqlParameter>
    {
        new SqlParameter { ParameterName = "@EmployeeId", Value = employee.EmployeeID },
        new SqlParameter { ParameterName = "@FirstName ", Value = employee.FirstName },
        new SqlParameter { ParameterName = "@LastName", Value = employee.LastName},
        new SqlParameter { ParameterName = "@MiddleInitial", Value = employee.MiddleName }
    };
db.Database.ExecuteSqlRaw("EXEC dbo.Employee_Update", sqlParms.ToArray());

Update

Stored Proc Parameters

ALTER PROCEDURE [dbo].[Employee_Update]
(
    @EmployeeId varchar(8),
    @FirstName varchar(40),
    @LastName varchar(40),
    @MiddleInitial char(1)
)

Did you check that employeeId isn't null? Did you check the the SQL parameter is providing the same number type, as your stored procedure is expecting? Say "EmployeeId" is int, but the stored procedure is expecting bigint — Morten Bork
– Morten Bork, Commented Mar 25, 2022 at 20:44
Let me know if you found the problem from this, but this just seems like something you should check first... Are you working with multiple databases? sometimes you will have a local one, and a deployed one, and if you have made a change to one, but not the other, this can also cause quite a bit of confusion. But I don't see directly how this could apply here... But you never know? — Morten Bork
– Morten Bork, Commented Mar 25, 2022 at 20:48
@MortenBork I updated my post with a screen shot. I can see the EmployeeId value with a value of type string. I'm still not getting it and probably something stupid I'm overlooking but just not seeing it. What I see is it passing in the parameters as it should. It's obviously getting to the database and SP so it shouldn't be a permissions issue. I'm baffled at the moment. — Caverman
– Caverman, Commented Mar 25, 2022 at 23:26
Hi Caverman, did you try the suggested solution this seems like a possible reason also. — Morten Bork
– Morten Bork, Commented Mar 26, 2022 at 9:15

Average Thumbs · Accepted Answer · 2022-03-26 04:30:37Z

8

Even though you created the stored procedure with ordered parameters, EF's ExecuteSQLRaw() function appears to require you to pass the parameters via SQL parameterization in the query string you're providing to the function.

Also ensure that your parameters are in the same order as your SQLParameter[] array.

Try modifying your execute line to this:

db.Database.ExecuteSqlRaw("EXEC dbo.Employee_Update @EmployeeId, @FirstName, @LastName, @MiddleInitial", sqlParms.ToArray());

Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

See the following link for more info: https://learn.microsoft.com/en-us/ef/core/querying/raw-sql

answered Mar 26, 2022 at 4:30

Average Thumbs

1164 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Caverman Over a year ago

Perfect! That's what I was missing.

Collectives™ on Stack Overflow

Entity Framework .Net 6 with SQL Server stored procedure – parameter not supplied

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related