What is the correct syntax for using Database.ExecuteSqlCommand with parameters on mysql?

Question

Execute mysql stored procedure

  var affectedRows = 
      context.Database.ExecuteSqlCommand("exec UpdateStatus @p_customerId, @p_id",
            new MySqlParameter("p_customerId", "006"),
            new MySqlParameter("p_id", 9));

Get MySqlException

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in EntityFramework.dll

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exec UpdateStatus '006', 9' at line 1

When I try to replace stored procedure name with a Update statement it works. But I want to call a stored procedure instead.

I tried using Entity Framework 5 and 6 same error

Chetan · Accepted Answer · 2017-03-08 18:14:30Z

1

MySQL Syntax of calling Stored Procedure from command is "CALL ". Also the parameters names should be having "@" in front.

So you need to change the code as as following.

var affectedRows = 
  context.Database.ExecuteSqlCommand("CALL UpdateStatus (@p_customerId, @p_id)",
        new MySqlParameter("@p_customerId", "006"),
        new MySqlParameter("@p_id", 9));

edited Mar 8, 2017 at 18:14

answered Mar 8, 2017 at 18:04

Chetan

6,9314 gold badges24 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

What is the correct syntax for using Database.ExecuteSqlCommand with parameters on mysql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related