Return error from query before finishing it

Question

I have following mysql script:

start transaction;
set autocommit = 0;
call RollbackRemove(1, 10);
call RollbackRemove(2, 5000);
commit;

and a procedure:

create procedure RollbackRemove(a INT, b NUMERIC(8, 0))
begin
    declare ret bool;
    select Remove(a, b) into ret;
    if not ret then
        rollback;
    end if;
end //

It tries to remove certain amount from each item, and if one remove fails, the transaction is rollbacked (inside procedure). However, I have noticed that even after calling rollback the script continues execution but that is not desired. Also I would like to be able to report an error (this will be called from php). I have been searching for mechanism that would end script sooner, something like return in other languages, but haven't found anything. What should I use in such situation?

You could use [exit()](http://php.net/manual/en/function.exit.php). — Davide Pastore, Nov 03 '14 at 16:30
perhaps this one: http://stackoverflow.com/a/1275431/650405 ? ugly as ... — Karoly Horvath, Nov 03 '14 at 16:32
@Hanky웃Panky yes this is all MySQL, only there is a redefined delimiter to // for procedure. — Raven, Nov 03 '14 at 16:35

score 1 · Accepted Answer · answered Nov 03 '14 at 16:32

1

Maybe you can use SIGNAL to raise an error.

answered Nov 03 '14 at 16:32

mgaido

2,987
3
17
39

does this stop the script? – Karoly Horvath Nov 03 '14 at 16:35
if you raise an error, it terminates the procedure. If you raise a warning, no. – mgaido Nov 03 '14 at 16:38
Thank you for fast reaction, the solution was to put `SIGNAL SQLSTATE '45000'` under rollback in procedure, this will leave database in state before transaction and ends the script while also generating error, just what I needed. – Raven Nov 03 '14 at 16:54

Return error from query before finishing it

1 Answers1