Conditional parts of MySQL script

Question

Sometimes I write long queries or a series of queries and save them as an SQL file to run later. Sometimes there are portions of the queries that I end up commenting out so that I can get slightly different results. I am wondering if there is some mechanism I can use directly inside a query/script to control which lines of code are run. Here is a very simple example:

SET @report = 1; 

SELECT
    c1,
    c2,
    c3
FROM
    table1

##if @report = 1##
WHERE
    c1 = true
    and c2 = true
GROUP BY
    c1

##if @report = 2##
WHERE
    c1 = false
    and c2 = true
GROUP BY
    c1,
    c2
;

Additional Note I should have mentioned that most of the time these would be run from MySQL Workbench. Though it could be run from another IDE/tool.

ANSWER Both Drew and Sasha Pachev gave good answers and both would be worth looking at.

Stored procedures
Prepared statements

not really. mysql isn't scriptable in this fashion. if you don't want mysql to execute a command(s), then don't feed them to mysql in the first place — Marc B
– Marc B, Commented Nov 10, 2015 at 20:29
@MarcB I assume this is the case but if there is something then it would be neat to find out. It could save some time. I could also do a stored procedure or actually use a programming language to build the query but again, maybe there is some trick out there that I don't know about. — donL
– donL, Commented Nov 10, 2015 at 20:32

Drew · Accepted Answer · 2015-11-10 21:02:37Z

1

Jam it into a stored proc. Revisit them and tweak them. Drop them. They can be your little sandboxes to use when you want.

When you use procs and functions, then if then else statements are opened up to you.

Stored Proc

drop procedure if exists doTodaysThing001;
DELIMITER $$
CREATE procedure doTodaysThing001 (IN someParam INT)
BEGIN

IF (someParam=7) then
     select "seven";
     -- ...
     -- ...
END IF;

-- some other stuff

if someParam=3 then
    select "three";
END IF;
END
$$
DELIMITER ;

Test it

call doTodaysThing001(3);

The other aspect would be prepared statements, also available inside stored procs directly, using concat() and binding. If you need help with that, just ask.

answered Nov 10, 2015 at 21:02

Drew

25k10 gold badges47 silver badges81 bronze badges

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

1 Comment

Drew Over a year ago

Here is a little reflection on the concat and bind discussion in this answer I wrote up

Sasha Pachev · Accepted Answer · 2015-11-10 20:51:50Z

1

This can be done with a little trick. Using prepared statements it is possible to build queries and then execute them. Consider the following example to get started:

set @q:=(select "select ? + 1 as n");
prepare  s from @q;
set @a:=1;
execute s using @a;

The first statement generates a query (you could put tables in it combined with the results of a select), then prepare sets up statement s, then execute executes it replacing ? with the value of @a.

answered Nov 10, 2015 at 20:51

Sasha Pachev

5,3363 gold badges23 silver badges20 bronze badges

1 Comment

donL Over a year ago

Prepared statements escaped my mind since I use the so infrequently. I did not know though that parameters can be passed. I will need to look into this.

Collectives™ on Stack Overflow

Conditional parts of MySQL script

2 Answers 2

Stored Proc

Test it

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Stored Proc

Test it

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related