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?
Try
SET @myId := 100;
Then if you do
select @myId;
You will get 100
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');
DROP PROCEDURE sp1;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 ;
DECLAREis only valid in stored programs, and "DECLARE is permitted only inside a BEGIN ... END compound statement".