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 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
– FDavidov, Commented Nov 17, 2016 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
– Alexander Kiselev, Commented Nov 17, 2016 at 15:06

Man · Accepted Answer · 2016-11-19 09:49:19Z

6

+50

    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.

answered Nov 19, 2016 at 9:49

Man

7721 gold badge6 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kumar_Vikas · Accepted Answer · 2016-11-14 10:47:05Z

1

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, 2016 at 10:47

Kumar_Vikas

8457 silver badges16 bronze badges

3 Comments

Alexander Kiselev Over a year ago

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"

Kumar_Vikas Over a year ago

Check for the syntax. might have missed something.

Alexander Kiselev Over a year ago

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;

Collectives™ on Stack Overflow

MySql conditional stop script execution

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related