0

I've read many threads here about mysql loops that throw errors. So do mine but neither of the solutions I've tried seem to fix my problem.

I'm working with some stored procedures and I'm trying to get the following loop to work:

DECLARE counter INT(11) DEFAULT 0; 

add_rows: LOOP
    INSERT INTO vote_orders (id, vote_id, vote_order, vote_candidate)
    WHILE counter <= @number_of_candidates DO               
        SELECT NULL, vote_id, counter, vote_candidate_a
        FROM votes_copy
        IF counter = @number_of_candidates THEN
            LEAVE add_rows;
        END IF;
        UNION
    END WHILE;
END LOOP add_rows;

This always throws the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE counter INT(11) DEFAULT 0;

add_rows: LOOP
    INSERT INTO vote_orde' at line 30

I've tried to only store the declaration of 'counter' which seems to work perfect but as soon as I try to add the loop it fails. (@number_of_candidates is a stored procedure that's called from the loop, the UNION is supposed to union each selects until counter reaches @number_of_candidates).

Do you have any suggestions on why this won't work?

Kristofer Gisslén
  • 1,252
  • 1
  • 11
  • 28
  • Do you have a `DELIMITER` statement before the procedure, so that you can use `;` inside the procedure? – Barmar Jul 05 '14 at 16:01
  • See http://stackoverflow.com/questions/10259504/delimiters-in-mysql – Barmar Jul 05 '14 at 16:08
  • I'm using phpMyAdmin which for some reason don't allow `DELIMITER`. I tried [this solution](http://stackoverflow.com/a/8081096/3305017) which solved the problem I had with storing the procedure. I'm using `//` as a delimiter inside phpMyAdmin. – Kristofer Gisslén Jul 05 '14 at 16:21
  • Why did you mark this as an duplicate @Barmar? The reference I added (which clearly suggests that I am well aware of the question) was a reply to your comment. Most likely I won't get any answers or helpful tips at all now that you (quite falsly) marked this as a duplicate to a question that doesn't cover my problem at all. It is not a delimiter related problem but rather a problem about how I handle the loop... – Kristofer Gisslén Jul 05 '14 at 16:52
  • You said the answer there solved your problem. – Barmar Jul 05 '14 at 16:58
  • 1
    You can't have an `IF` statement in a query, it has to be a statement of its own. And I don't think you can have `UNION` all by itself like that. – Barmar Jul 05 '14 at 17:00
  • I might have been unclear on what I meant: The reference solved the problem of not being able to use `DELIMITER` in phpMyAdmin. But as you can see of the question in that reference it has nothing to do with loops. Even the title suggests the question covers a different area: _Store procedures in phpMyAdmin_. – Kristofer Gisslén Jul 05 '14 at 17:02
  • 1
    I misunderstood. I thought the reason you were having a problem with the loop was because you didn't do the delimiter correctly. I've reopened the question. – Barmar Jul 05 '14 at 17:03
  • About the `IF` statement: This is a really good tip. I didn't know that it wasn't allowed inside a query. I must look into that. About the `UNION`: How would I best perform what I'm aiming at? – Kristofer Gisslén Jul 05 '14 at 17:05
  • Create a temporary table, and insert the results of each `SELECT` into the table. Then at the end of the procedure return `SELECT * FROM tempTable`. – Barmar Jul 05 '14 at 17:07

0 Answers0