CODEWITHSUNDEEP
mysqlif-statementstored-procedures
Melad Basilius
Melad Basilius

Reputation: 4306

How to exit from outer IF statement in MYSQL

How to exit from the outer IF statement, and continue the stored procedure

IF (true) THEN

    -- do some work here

    IF (somethingWrong=true) THEN
        -- Exit from the outer IF and continue SP
    END IF;

    -- do some work here
END IF;

Upvotes: 0

Views: 2364

Answers (1)

trincot
trincot

Reputation: 350866

You could just test the opposite condition:

IF (true) THEN
    /* do some work here */
    IF (NOT somethingWrong) THEN
        /* Do whatever is remaining to do in this outer IF block */
    END IF;
END IF;

In case you have multiple instances where you want to bail out like that, then you probably don't want to nest the IF blocks like this:

IF (true) THEN
    /* do some work here */
    IF (NOT somethingWrong) THEN
        /* do some more work here */
        IF (NOT somethingWrong) THEN
            /* Do whatever is remaining to do in this outer IF block */
        END;
    END IF;
END IF;

... this can get deeply nested. Instead use a variable (check the SET syntax) that keeps track of the error condition and flatten your IFs. Something like this:

SET err = 0
IF (true) THEN
    /* do some work here */
    IF (NOT err)
        /* do some more work here that maybe sets the err variable to some non-zero value */
    END IF;
    IF (NOT err)
        /* do some more work here that maybe sets the err variable non-zero */
    END IF;
    IF (NOT err)
        /* do the remaining work */
    END IF;
END IF;

You can even have some loops with the same principle in there:

IF (true) THEN
    /* do some work here */
    IF (NOT err)
        /* do some more work here that maybe sets the err variable to some non-zero value */
    END IF;
    IF (NOT err)
        /* do some more work here that maybe sets the err variable non-zero */
    END IF;
    WHILE (NOT err AND some_loop_condition) DO
        /* some work that needs to repeat a few times, 
           but should be interrupted when err is non-zero */
    END WHILE
    IF (NOT err)
        /* do the remaining work */
    END IF;
END IF;

Upvotes: 1

Related Questions