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.

Answer 2

After doing some research I think I may have found a way to work around this. I was looking for a way to verify if a script had already executed against a target database. This will be primarily for version control of my databases. I have a table created to keep track of the scripts that have been executed and wanted some flow inside my scripts to check that table first before execution. While I have not completely solved the problem yet I have created a simple script that basically does what I need, I just need to wrap the DDL into the selects based on the value of the variables.

step 1 - Setup a bit variable to hold the result step 2 - do your select and set the variable if the result is found step 3 - Do what you need to do on false result step 4 - Do what you need to do on true result

Here is the example script

set @schemachangeid = 0;

select @schemachangeid := 1 from SchemaChangeLog where scriptname = '1_create_tables.sql';

select 'scriptalreadyran' from dual where @schemachangeid = 1;

select 'scriptnotran' from dual where @schemachangeid = 0;

I also recognize this is an old thread but maybe this will help someone out there trying to do this kind of thing outside of a stored procedure like me.