CODEWITHSUNDEEP
mysqlsqlselectsql-scripts
Alexander Kiselev
Alexander Kiselev

Reputation: 446

MySql conditional stop script execution

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.

Upvotes: 2

Views: 4409

Answers (2)

Man
Man

Reputation: 772

    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.

Upvotes: 6

Kumar_Vikas
Kumar_Vikas

Reputation: 845

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;

Upvotes: 1

Related Questions