MySQL Select into variable

Question

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

score 5 · Accepted Answer · edited May 23 '17 at 11:46

5

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?

edited May 23 '17 at 11:46

Community

1
1

answered Nov 12 '12 at 21:16

Frank Thomas

2,434
1
17
28

Mhhh, tried it but doesn't work. The problem seems to be more related to prepare/execute/deallocate. If I execute the select into directly it works: "DECLARE myvar INT; DECLARE myvar2 INT; SELECT 1,2 into myvar, myvar2; SELECT myvar, myvar2;" but when I prepare the sql stmt first it doesn't seem to recognize the variable and it complaints about it not being declared: " DECLARE myvar INT; DECLARE myvar2 INT; SET @sql=CONCAT('SELECT 1,2 into myvar, myvar2'); PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1; SELECT myvar, myvar2;" – Leo Nov 12 '12 at 21:36
Found the solution now: http://forums.mysql.com/read.php?98,186645,186658#msg-186658 – Leo Nov 12 '12 at 22:21

score 2 · Answer 2 · answered Nov 12 '12 at 21:41

2

If you do not need the dynamic SQL inside your procedure, you can simpler syntax:

DECLARE v_1 INT;   DECLARE v_2 INT;   DECLARE v_3 INT;

SELECT 1,2 into v_1, v_2;
SET v_3 = v_1 + v_2;
SELECT v_3;

Using normal variables declared inside stored procedure are safer than the user-defined variables (@-variables) as a call to another stored procedure may change your user-defined variable value.

answered Nov 12 '12 at 21:41

slaakso

8,331
2
16
27

Thank you slaakso, the thing is that I need the dynamic sql, I need to prepare the query first dynamically due to selecting from different tables and other conditions. – Leo Nov 12 '12 at 21:45

score 0 · Answer 3 · answered Dec 30 '16 at 04:43

0

You need to modify little code to work properly see my modification

DECLARE v_1 INT;
DECLARE v_2 INT;
DECLARE v_3 INT;

SET @sql=CONCAT('SELECT 1,2 into @v1, @v2');
PREPARE s1 FROM @sql;
EXECUTE s1;

SELECT @v1, @v2 INTO v_1, v_2;
SET v_3 = v_1 + v_2;
SELECT v_3;

DEALLOCATE PREPARE s1;

answered Dec 30 '16 at 04:43

Alice

1
3

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Dec 28 '17 at 13:35

MySQL Select into variable

3 Answers3

Linked