Loop & increment values on both sides of an expression

Question

My goal is to create a series of "SET" commands using the LOOP command:

SET @value_title_1 = value_1
SET @value_title_2 = value_2
...
SET @value_title_99 = value_99

the numbering (counter) needs to increase by equal amount of 1 on both sides of the SET command.

The following is a representation of the code I am working with and also includes the "dummy entry" (you will recognize this by SET concat on the LEFT side of the equasion, which is the tricky part)

delimiter $$ 
 CREATE PROCEDURE set_values_table()
 DETERMINISTIC
 BEGIN
 DECLARE counter INT DEFAULT 1;

simple_loop: LOOP
    SET counter=counter+1;
    SET concat('@value_title_',counter) = concat('value_',counter);
IF counter = 99 THEN
 LEAVE simple_loop;
 END IF;
 END LOOP simple_loop;
 SELECT "procedure completed!";
 END$$

 delimiter ;

call set_values_table();

Unfortunately, the SET concat(...) yields a syntax error. Any direction or step closer to solution would be much appreciated! Or is there another way to accomplish the same result as stated above?

Hi Stan, it's a little unclear what you're trying to do in your question. Do you want to build up a string containing all the `SET` statements? I don't think you can use SQL the way you want using a string in place of the variable identifier. — TZHX, Apr 17 '15 at 10:27
I'm fairly sure that what you want to do (the way you want to do it), just isn't possible, unfortunately. — TZHX, Apr 17 '15 at 10:34
Hi @TZHX - Ravinder below came pretty close, however, the output is relayed as a non-executable string only. I need each of the created statements to actually truly "SET" the value as given so I could retrieve each of them separately right after with SELECT commands. — Stan, Apr 17 '15 at 19:21
Stan: maybe if you build on Ravinder's script (which is along the lines of what I was thinking could be done) and take a look at the [question and answer here](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) about running dynamic sql statements in a procedure, you'll be able to do something with it? I'm not a regular user of mysql I'm afraid so I'm not sure if they'd stay in scope after it's executed in that way. HTH. — TZHX, Apr 17 '15 at 19:52
Indeed! TZHX I was able to combine what you suggested. And... I got it working!! I will go ahead and mark the answer accepted by @Ravinder and extend kudos to you for directing me to the last missing piece! Cheers! — Stan, Apr 17 '15 at 21:46

score 1 · Accepted Answer · answered Apr 17 '15 at 10:39

SET command can only be used to set values for variables using their names, but not expressions.

If I understand correct, you must be trying to generate 99 SET commands to set values for 99 variables and use the same output in your code.

You need part of your SP body part changed as below (I used loop count as 5):

BEGIN
  DECLARE counter INT DEFAULT 1;
  set @result := '';

  simple_loop: LOOP
    -- SET concat('@value_title_',counter) = concat('value_',counter);
    set @result := concat( @result, '\nset @value_title_', counter );
    set @result := concat( @result, ' := value_', counter, ';' );
    IF counter = 5 THEN
      LEAVE simple_loop;
    END IF;
    SET counter=counter+1;
  END LOOP simple_loop;
  -- SELECT 'procedure completed!';
  SELECT @result as result;
END;

You can check the output as below:

mysql> select @result as res\G
*************************** 1. row ***************************
res:
set @value_title_1 := value_1;
set @value_title_2 := value_2;
set @value_title_3 := value_3;
set @value_title_4 := value_4;
set @value_title_5 := value_5;

You can use the @result variable value as desired.

This is much appreciated, @Ravinder ! However, my intention is for these SET commands to actually "execute" and truly "set" the values as given. In other words, I should then right after be able to run the SELECT command, such as mysql> select @value_title_1; but this gives me NULL as return, not the actual value ("value_1") it was just assigned. Would that be possible somehow? — Stan, Apr 17 '15 at 19:22

Loop & increment values on both sides of an expression

1 Answers1