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?
Advertisement
Answer
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