database - If conditional in SQL Script for Mysql

Question

In a sql script that does sequential execution, is there a way one can introduce an IF THEN ELSE conditional to control the flow of query execution?

I happened to run into this http://www.bennadel.com/blog/1340-MySQL-Does-Not-Support-IF-ELSE-Statements-In-General-SQL-Work-Flow.htm which says that the IF THEN ELSE will not work in a sql script.

Is there another way around?

Basically, I want to run a particular "select colName from table" command and check if colName corresponds to a particular value. If it does, proceed with the rest of the script. Else, halt execution.

Please advise.

Answer 1

I just wrap my SQL script in a procedure, where conditional code is allowed. If you'd rather not leave the statements lying around, you can drop the procedure when you're done. Here's an example:

delimiter //

create procedure insert_games() 

begin

    set @platform_id := (select id from platform where name = 'Nintendo DS');

    -- Only insert rows if the platform was found
    if @platform_id is not null then 

        insert into game(name, platform_id) values('New Super Mario Bros', @platform_id);
        insert into game(name, platform_id) values('Mario Kart DS', @platform_id);

    end if;

end;

//

delimiter ;

-- Execute the procedure
call insert_games();

-- Drop the procedure
drop procedure insert_games;

If you haven't used procedures, the "delimiter" keyword might need some explanation. The first line switches the delimiter to "//" so that we can include semi-colons in our procedure definition without MySQL attempting to interpret them yet. Once the procedure has been created, we switch the delimiter back to ";" so we can execute statements as usual.