MYSQL Stored Procedure Variables in While loop

Question

Ok this may be super simple but I cannot seem to find an answer. I am currently trying to clean up some storeod procedures that use a bunch of select statements with variables that only increment in number such as. id1, id2, id3, id4, id5, id6, id7 and id8. Instead of calling upwards of 24 statements in a row, I would like to cut the stored procedure down to just a few lines.

Now please excuse me if syntax is way off, not a MySQL programmer by trade.

In my normal languages I would do a while loop with a the variable and the control attached such as.

while x <= count do

 select * from table where col = id[x];

 SET x = x + 1;


end;

what is the proper way to attach the control to the end of the variable? is this possible in a stored procedure at all? Select statements are a little more than the one shown but if I can do a simple one like that it will work for the rest of the statement.

Code currently used, this is only part of the code. I just posting one with the 3 different select statements. Currently using 3 different basic select into statements. If I can somehow combine all 3 into 1 statement would be amazing but I am newer to MySQL, maybe given some more time? if (licount <=2 ) then /* 2 Stations */ select gen_idx into gen_idx1 from j_final_results where line=pline and station=1 and type_result=1 order by ID desc limit 1; select gen_idx into gen_idx2 from j_final_results where line=pline and station=2 and type_result=1 order by ID desc limit 1; set gen_idx3=0; set gen_idx4=0; set gen_idx5=0; set gen_idx6=0; set gen_idx7=0; set gen_idx8=0; select scan_lbl into serial1 from j_final_results where line=pline and station=1 and type_result=1 order by ID desc limit 1; select scan_lbl into serial2 from j_final_results where line=pline and station=2 and type_result=1 order by ID desc limit 1; set serial3=''; set serial4=''; set serial5=''; set serial6=''; set serial7=''; set serial8=''; select type_result into type1 from j_final_results where line=pline and station=1 order by ID desc limit 1; select type_result into type2 from j_final_results where line=pline and station=2 order by ID desc limit 1; set type3=0; set type4=0; set type5=0; set type6=0; set type7=0; set type8=0;

You can do soemthing similar in a procedure,whats the problem? — Mihai
– Mihai, Commented Jan 15, 2015 at 14:14
I am just trying ot figure out the correct way to do id[x]? if that is the way than awesome. — Ralre
– Ralre, Commented Jan 15, 2015 at 14:16

Giles · Accepted Answer · 2015-01-15 14:32:46Z

1

You need to do some dynamic code creation, my preferred method would look something like this:

WHILE X <= COUNT DO


    SET @sql = CONCAT('select * from table where col = id',X);
    PREPARE runme FROM @sql;
    EXECUTE runme;
    DEALLOCATE PREPARE runme;

    SET X = X + 1;


END;

You can also put a ? in the @sql string and pass X in with USING - see here:sql-syntax-prepared-statements

answered Jan 15, 2015 at 14:32

Giles

1,69713 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ralre Over a year ago

I think this will work for what I need, I have one question though. I have some varibles that get passed into the procedure. I need to use that variable inside the statement too. The variable is shown above as 'pline'. How do i use this inside the prepared statement? I also have a problem on the id[x] part as it keeps saying variable does not exist.

Ralre Over a year ago

I believe I figured it out. I appreciate the help, this worked nicely!

Giles Over a year ago

Glad it worked out, sorry I did not get back sooner, had to go off-line.

Collectives™ on Stack Overflow

MYSQL Stored Procedure Variables in While loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related