Declare the invalid variable syntax in MySQL Workbench?

advertisements

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?


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