Declare variable syntax invalid in MySQL Workbench?

mysql syntax-error mysql-workbench declare
Roy Hinkley picture Roy Hinkley · Jan 30, 2014 · Viewed 24.2k times · Source

I am trying to create and set a variable:

DECLARE myId INT;
SET myId = 5;

However, I am getting invalid syntax complaint in MySQL Workbench:

SQL syntax error near 'DECLARE myId INT;'

I have tried the following variants:

DECLARE myId INT(4);
SET myId = 5;

DECLARE @myId INT;
SET @myId = 5;

DECLARE @myId INT(4);
SET @myId = 5;

What is wrong?

Answer

Angelo Saleh picture Angelo Saleh · Jan 30, 2014

As in the comment says Declare is only valid into stored programs like procedures, functions. here you have an example of a store procedure and its call.

DELIMITER $$

CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE myId INT;


  SET myId = 5;
  SELECT CONCAT(xname,' -- ',myId);
END;
$$

DELIMITER ;

call sp1('MY NAME');

Related questions

How can I fix MySQL error #1064?

When issuing a command to MySQL, I'm getting error #1064 "syntax error". What does it mean? How can I fix it?

Read More
mysql error 'TYPE=MyISAM'

Below query I'm executing in Ubuntu 12, MySQL 5.1 version and receiving error as mentioned: CREATE TABLE mantis_config_table ( config_id VARCHAR(64) NOT NULL, project_id INTEGER NOT NULL DEFAULT 0, user_id INTEGER NOT NULL DEFAULT 0, access_reqd INTEGER DEFAULT 0, type …

Read More
mysql_error() not displaying an error

I am trying to debug my code but mysql_error() isn't displaying anything. I know there is something wrong, because when I write or die("ERROR"); It displays ERROR. So the problem must be with that line of code. When …

Read More