Error on DECLARE variable in MySql procedure

Question

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?

Abhik Chakraborty · Accepted Answer · 2014-06-22 09:02:01Z

3

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 ;

answered Jun 22, 2014 at 9:02

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

2 Comments

TribalChief Over a year ago

When using phpMyAdmin, I had to begin my code with a delimiter, i.e. ; right before specifying a different delimiter (i.e. //, in this case).

Abhik Chakraborty Over a year ago

; will be treated as inidividual mysql statements, so to enclose the procedure and statements consist of ; you will need to define a delimiter before the procedure and trigger so that everything inside the delimiter is processed without any confusion.

Collectives™ on Stack Overflow

Error on DECLARE variable in MySql procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related