How to use goto label in MySQL stored function

goto, mysql, stored-functions

Solution

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).

Problem

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; ```

Original source