MySQL Select into variable

mysql execute prepare
Leo picture Leo · Nov 12, 2012 · Viewed 14.9k times · Source

I want to further use in the procedure the values I get from a select into execution but can't figure out how to do it.

As a test I wrote the following but cannot use the v_1, v_2 or v_3 variables for further logic as they don't take the values 1,2 & 3 as i expected...

DROP PROCEDURE IF EXISTS MPT_testing; DELIMITER //  CREATE PROCEDURE MPT_testing() READS SQL DATA BEGIN

  DECLARE v_1 INT;   DECLARE v_2 INT;   DECLARE v_3 INT;
     SET @sql=CONCAT('SELECT 1,2 into v_1, v_2');   PREPARE s1 FROM @sql;   EXECUTE s1;   DEALLOCATE PREPARE s1;

  SET v_3 = v_1 + v_2;

  SELECT v_3;

END //

DELIMITER ;

Can somebody help here please?

thanks, Leo

Answer

Frank Thomas picture Frank Thomas · Nov 12, 2012

Try changing 'SELECT 1,2 into v_1, v_2' to 

'SELECT @v_1:=1, @v_2:=2'

or at least make sure you use @ whenever referencing your vars. see this thread for more info: SELECT INTO Variable in MySQL DECLARE causes syntax error?

Related questions

Is it possible to execute a string in MySQL?

I have to convert a MSSQL stored proc that passes a varchar that is a query: INSERT INTO Results EXEC (@Expresion); This isn't working. I'm pretty sure that EXEC and EXECUTE aren't MySQL commands, but CALL doesn't work either. Does …

Read More
Execute multiple semi-colon separated query using mysql Prepared Statement

I am trying to create a stored procedure in mysql which creates a new table on every request copies the content from another table and extracts the required data and finally drops the table. The stored procedure is quite large …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More