For my MySQL PROCEDURE
, I have a select query statement using WHERE IN (...)
clause, for which I need some conditional inputs/variables.
example:
SELECT * FROM test_table tt WHERE tt.section IN ('A', 'B', 'C');
SELECT * FROM test_table tt WHERE tt.section IN ('A', 'B', 'D');
SELECT * FROM test_table tt WHERE tt.section IN ('A','C');
SELECT * FROM test_table tt WHERE tt.section IN ('A', 'B', 'C', 'D');
How can I achieve this using a single query?
Note: This select query statement is inside mySQL PROCEDURE
.
I'm looking for something like this:
SET @ABC = '"A", "B", "C"';
SET @ABCD = '"A", "B", "C", "D"';
SET @AC = '"A", "B", "C", "D"';
SET @IN_CLAOUSE_VARIABLES = NULL;
IF (<CONDITION 1>) THEN
SET @IN_CLAOUSE_VARIABLES = @ABC;
ELSEIF (<CONDITION 2>) THEN
SET @IN_CLAOUSE_VARIABLES = @ABCD;
ELSE
SET @IN_CLAOUSE_VARIABLES = @AC;
END IF:
SELECT * FROM test_table tt WHERE tt.section IN (@IN_CLAOUSE_VARIABLES);
SET @ABC = 'A,B,C';
SET @ABCD = 'A,B,C,D';
SET @AC = 'A,C';
SET @IN_CLAOUSE_VARIABLES = NULL;
IF (<CONDITION 1>) THEN
SET @IN_CLAOUSE_VARIABLES = @ABC;
ELSEIF (<CONDITION 2>) THEN
SET @IN_CLAOUSE_VARIABLES = @ABCD;
ELSE
SET @IN_CLAOUSE_VARIABLES = @AC;
END IF:
SELECT * FROM test_table tt WHERE FIND_IN_SET(tt.section, @IN_CLAOUSE_VARIABLES);
tt.section
must NOT contain commas and not need to be quoted with "
. I.e. it may be 'A'
(will be found in all variants), 'D'
(only when <CONDITION 1>
is false and <CONDITION 2>
is true) or 'X'
(never).