Search code examples
mysqlselectmysql-workbenchdeclare

SELECT INTO Variable in MySQL DECLARE causes syntax error?

I´d like to SELECT a single value into a variable. I´d tried to following:

DECLARE myvar INT(4);

-- immediately returns some syntax error.

SELECT myvalue 
  FROM mytable 
 WHERE anothervalue = 1;

-- returns a single integer

SELECT myvalue 
  INTO myvar 
  FROM mytable 
 WHERE anothervalue = 1;

-- does not work, also tried @myvar

Is possible to use DECLARE outside of stored procedures or functions?

Maybe I just dont get the concept of user variables... I just tried:

SELECT myvalue INTO @var FROM `mytable` WHERE uid = 1;
SELECT @var;

...which worked just like it´s supposed to. But if I run each query at a time i just get @var NULL.

Solution

  • In the end a stored procedure was the solution for my problem.

    Here´s what helped:

    DELIMITER //

CREATE PROCEDURE test ()
BEGIN
  DECLARE myvar DOUBLE;
  SELECT somevalue INTO myvar FROM mytable WHERE uid = 1;

  SELECT myvar;
END//

DELIMITER ;

call test();