How to use goto label in MySQL stored function

Question

I would like to use goto in MySQL stored function. How can I use? Sample code is:

if (action = 'D') then
    if (rowcount > 0) then
        DELETE FROM datatable WHERE id = 2;      
    else
       SET p=CONCAT('Can not delete',@b);
       goto ret_label;
    end if;
end if;

Label: ret_label;
return 0;

Tass

Answer 1

There are GOTO cases which can't be implemented in MySQL, like jumping backwards in code (and a good thing, too).

But for something like your example where you want to jump out of everything to a final series of statements, you can create a BEGIN / END block surrounding the code to jump out of:

aBlock:BEGIN
    if (action = 'D') then
        if (rowcount > 0) then
            DELETE FROM datatable WHERE id = 2;      
        else
           SET p=CONCAT('Can not delete',@b);
           LEAVE aBlock;
        end if;
    end if;
END aBlock;
return 0;

Since your code is just some nested IFs, the construct is unnecessary in the given code. But it makes more sense for LOOP/WHILE/REPEAT to avoid multiple RETURN statements from inside a loop and to consolidate final processing (a little like TRY / FINALLY).

Anaclinal

Answer 2

There is no GOTO in MySQL Stored Procs. You can refer to this post: MySQL :: Re: Goto Statement

Isomagnetic