MySQL Calling A Stored Procedure Error

Question

I am really hoping someone can help me out.

I had a SQL Server 2014 database which I used for a desktop application I wrote. After having to expand, I want to convert to MySQL to access the database over the internet.

I user MySQL WorkBench Migration Tool to copy all the tables and data from the SQL Server to the MySQL Server. The only problem is the procedures would not copy, so had to modify them manually.

Example of a Procedure in MySQL:

DELIMITER $$
CREATE DEFINER=`johandre`@`%` PROCEDURE `sp_GetAllOrdersBySuburb`( IN `@SuburbID` VARCHAR(50) )
NO SQL
SELECT * from Orders  WHERE DeliverySuburb = @SuburbID$$
DELIMITER ;

The server created the procedures, and all procedures not using IN inputs do show what they must, but the procedures that user inputs give me an error: When calling from PhpMyAdmin SQL: Error

SQL query: Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation

2014 - Commands out of sync; you can't run this command now

And when I run the procedure in C# Winforms App, it just returns a empty result set.

The code I used to call the procedure:

SET @p0='1'; CALL `sp_GetAllOrdersBySuburb`(@p0);

When I run the code in the procedure as a normal SQL query, then it also returns the data as expected.

I hope this is enough information, and hope this isn't a repeat question, but I did look around and still found no help.

Thank You

Answer 1

I think, your problem might be your delimiter when defining the procedure. Also, when using backticks to define your param (otherwise @ won't be allowed), you need them when accessing the param, too:

DELIMITER $$ /* make $$ the delimiter for actually executing something */

CREATE DEFINER=`johandre`@`%` PROCEDURE `sp_GetAllOrdersBySuburb`( IN `@SuburbID` VARCHAR(50) )
NO SQL
SELECT * from Orders  WHERE DeliverySuburb = `@SuburbID`; /* normal ; here, you just define your proc */

$$ /* now execute the definition */

DELIMITER ; /* reset the delimiter */

For the delimiters:

You change your delimiter because you don't want any ; in your procedure being interpreted as an execution delimiter but being part of your procedure instead. After that, you want to execute the whole definition and then reset the delimiter.

For @ in parameters:

@ is a reserved character only for accessing global variables. If you really want to use @ in your param, you need backticks to make this work. Backticks allow you to use white spaces, reserved words and even strange characters or those who have a special meaning in regular syntax and are not allowed in an identifier otherwise to be used within identifiers anyway. However, you have to use backticks for a correct dereference as well then.

`@identifier`

and

@identifier

resolve to different things. That means, you need

SELECT * from Orders  WHERE DeliverySuburb = `@SuburbID`;

instead of

 SELECT * from Orders  WHERE DeliverySuburb = @SuburbID;