MySql conditional stop script execution

Question

I need to write SQL script which stops if some values not found in DB. Something like this (pseudo-code):

BEGIN;
...
set @a = (select ... );
if @a IS NULL THEN STOP_WITH_ERROR_AND_ROLLBACK();
...
COMMIT;

Can anybody help to me?

UPDATE: for some reasons, I can't use stored procedures or functions.

UPDATE 2: Note: I don't need explicit rollback. Break of script execution are sufficiently. It automatically rollback changes of not-committed transaction.

MySQL allows you to declare error handlers WITHIN a stored program (e.g. `DECLARE EXIT HANDLER FOR NOT FOUND...`; see [here](http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html) which can execute a specific code you may need before exiting. You are saying that you cannot use a stored procedure/function. Could you explain why? — FDavidov, Nov 17 '16 at 14:27
I write script, which our DB administrator will use for edit of our DB. As rule, script runs from remote machine, but for security reasons, changing and/or creating stored procedures from remote connections disabled at our MySql Server. Note: I don't need explicit rollback. Break of script execution are sufficiently. It automatically rollback changes of not-committed transaction. — Alexander Kiselev, Nov 17 '16 at 15:06

score 5 · Accepted Answer · answered Nov 19 '16 at 09:49

    start transaction;

    set @a=(select ...);
     -- whatever you want to execute your code like insert, update

    set @b=(select if(@a is null,'ROLLBACK','COMMIT'));
    -- @b is store **ROLLBACK** if @a is null nither store **COMMIT**

    prepare statement1 from @b;
    -- now statement1 store as @a value
    execute statement1;

i hope it solve your problem.

score 2 · Answer 2 · answered Nov 14 '16 at 10:47

2

This should get you going. Do some research on how to use transaction is mySQL.

START TRANSACTION
...
set @a = (select ... );
IF @a IS NULL 
THEN
 SELECT 'Error Message' as ErrorMsg
 ROLLBACK;
END IF;
...
COMMIT;

answered Nov 14 '16 at 10:47

Kumar_Vikas

837
7
16

If I'm not mistake, the "IF" statement can be used only for stored procedures. I get error message: "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 'IF @CplxSecDirHdrId IS NULL THEN SELECT 'Error Message' as ErrorMsg ROLLBACK' at line 2" – Alexander Kiselev Nov 14 '16 at 11:09
Check for the syntax. might have missed something. – Kumar_Vikas Nov 14 '16 at 11:22
This simple script got a error: use test; START TRANSACTION; set @a = (select 1); IF @a IS NULL THEN SELECT 'Error Message' as ErrorMsg ROLLBACK; END IF; COMMIT; – Alexander Kiselev Nov 14 '16 at 11:29

MySql conditional stop script execution

2 Answers2