CODEWITHSUNDEEP
mysqlsqldatabasestored-procedures
Fred K
Fred K

Reputation: 13910

Error on DECLARE variable in MySql procedure

I'm trying to create a simple stored procedure like this:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE testProcedure()
BEGIN

  DECLARE variableAaa INT;
  DECLARE variableBbb INT;

END

but I get this error from MySql:

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 'END' at line 1

If I change the variable names:

DECLARE variableA INT;
DECLARE variableB INT;

I get error:

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 'DECLARE variableB INT' at line 1

and so on:

DECLARE variableName INT;
DECLARE variableEmail INT;

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 'DECLARE variableEmail INT' at line 1

I'm using MySql 5.5.34 with PhpMyAdmin, but it's the same with Sequel Pro.

Where am I wrong?

Upvotes: 1

Views: 4748

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Looks like its an issue of delimiter which you are missing

So the procedure should look like

delimiter //
CREATE DEFINER = 'root'@'localhost'
PROCEDURE testProcedure()
BEGIN

  DECLARE variableAaa INT;
  DECLARE variableBbb INT;

END ; //

delimiter ;

Here I tried the same on MySql and it works

mysql> delimiter //
mysql> create procedure testProcedure()
    -> begin
    -> DECLARE variableAaa INT;
    -> DECLARE variableBbb INT;
    -> end; //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

Upvotes: 3

Related Questions