Matt Bannert Matt Bannert - 3 months ago 38
MySQL Question

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.

Answer

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