using conditional where clauses in mysql select statement

Question

There is a stored procedure sp_select_from_persons in MySql. And there are 3 parameters inside this procedure. age, class, group. These parameter values can be -1 or another value. And these parameters will be used in where clause.
Now I want to write where clause like that, if any value of these parameter is another than -1 then append them into where clause. For example:
if age = -1, class = 7 and group = 5 then select query will be:

SELECT * FROM persons p WHERE p.class = class AND p.group = group;

or if age = 25, class = -1 and group = -1 then:

SELECT * FROM persons p WHERE p.age = 25;

and etc.

I don't want to use emnedded IF statements like below:

IF age > 0 THEN 
  IF class > 0 THEN
     IF group > 0 THEN
     SELECT * FROM persons p WHERE p.age = age AND p.class = class AND p.group = group;
     END IF
     ELSE ....... etc

Is there a better way to do this?

Ravi · Accepted Answer · 2017-05-29 07:03:21Z

5

This is a single line query.... Try This

SELECT * FROM persons p 
WHERE p.age = case when age = -1 then p.age else age end
and p.class = case when class = -1 then p.class else class end
and p.group = case when group = -1 then p.group  else group end

answered May 29, 2017 at 7:03

Ravi

1,1721 gold badge9 silver badges23 bronze badges

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

Comments

Mario Garrido · Accepted Answer · 2017-09-30 05:03:20Z

3

Something like this also worked for me:

create procedure sp_my_proc(in p1 varchar(10), in p2 int)
    begin
        SELECT col1, col2 from my_table
         where if(p1 is null or length(trim(p1)) = 0, true, col1 = p1)
           and if(p2 = 0, true, col2 = p2);
    end

My requirement is that the stored proc is being called from a Java/SpringFramework-based back-end where passing null values to stored proc invocation is not accepted so values are set to empty string.

edited Sep 30, 2017 at 5:03

user1773603

answered Sep 30, 2017 at 4:40

Mario Garrido

1172 silver badges10 bronze badges

Comments

Mureinik · Accepted Answer · 2017-05-29 07:04:15Z

1

You can use the logical or operator to crate this behavior in a single statement:

SELECT *
FROM   persons p 
WHERE  (age < 0 OR p.age = age) AND 
       (class < 0 OR p.class = class) AND 
       (group < 0 OR p.group = group);

edited May 29, 2017 at 7:04

answered May 29, 2017 at 7:01

Mureinik

316k54 gold badges400 silver badges405 bronze badges

3 Comments

Nigel Ren Over a year ago

Should that be AND in (age > 0 OR p.age = age) otherwise it will always match if an age is passed in?

Mureinik Over a year ago

@NigelRen by bad - it's supposed to be "age < 0 OR p.age = age" - either you passed -1 (or you ignore p.age) or the age matches.

Web Developer India Over a year ago

Select * from myTable where (Negative Conditions OR Positive Conditions) AND (Negative Conditions OR Positive Conditions) ;

Collectives™ on Stack Overflow

using conditional where clauses in mysql select statement

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related