Declare variable syntax invalid in MySQL Workbench?

Question

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 1

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');

Answer 2

I experienced the same problem. The variables must be declared at the beginning of the script.

DELIMITER &&

DROP PROCEDURE IF EXISTS PS_HANDLERS;

CREATE PROCEDURE PS_HANDLERS(IN ID_USER INT, OUT isError INT)
    BEGIN
        DECLARE USER_EMAIL VARCHAR(50);
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
            BEGIN
                SET IsError = 1;
            END;

        SET USER_EMAIL = CONCAT(RAND(),'@',RAND(),'.com');
        SET isError = 0;



        INSERT INTO tbl_user VALUES(ID_USER, 'ipsum','lorem','[email protected]','password','ROLE_USER');

        SELECT
            u.*
        FROM 
            tbl_user u;
    END &&

DELIMITER ;