0

i want to write case statement in where clause in which i want to select record from table property_master except code='2' if request_type as input from procedure is 1 my procedure syntax is

create PROCEDURE test101 (IN request_type int)
begin
  select code from property_master where code=case when request_type=1 then code else 4 end;
end;

i want something like this

select * from property_master where code=case when request_type=1 then code <> '2'

in that request_type value i am getting from stored procedure as input so my intention is select all code from table except 2

Ritesh Fondke
  • 122
  • 1
  • 11
  • You almost never use `case` in `where` clauses. Use simple boolean logic. Please add an example and expected output – juergen d Apr 20 '18 at 07:08
  • See here for a similar question: https://stackoverflow.com/questions/9845171/run-a-query-in-a-mysql-stored-procedure-if-a-condition-is-true – Tim Biegeleisen Apr 22 '18 at 11:12

1 Answers1

0

It appears you just need some boolean logic, and you might need to combine some conditions using parentheses such as this:

select * from property_master
where code='1'
OR (request_type=1 AND code <> '2')
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • exactly what i want i also achieved same by select code from property_master where code <> case when request_type=1 then 2 else code end; – Ritesh Fondke Apr 22 '18 at 11:38
  • by this also i am getting same result select * from property_master where (request_type=1 AND code <> '2'); then 1) why there is need to add code='1' in where clause 2) even if i wrote code='2' also i am able to get same result. why? – Ritesh Fondke Apr 22 '18 at 12:08