MySql select statement with conditional where clause-CodePudding

I am trying to write a MySql statement with a conditional where clause. something like this:

set @price = 5000 ;
set @city = 1324368075;

 select count(*)
 from property
 where case when @price is not null then
              price < @price
       end
       and (case when @city is not null then
             CityId = @city
          end)

the variable should be included in the query only if it is not null.

My attempts have failed so far. Any ideas?

Edited:

Sorry I spoke too soon ysth,

these two queries are supposed to yield the same count but they dont.

Edit #2: Execution plan & indexes

Here's the query:

 set @CountryId = null ;
 set  @CityId = 1324368075 ;
 set @StateProvince = null ;
 set @CategoryId = null ; 
 set  @TransactionTypeId = null;
 set @Price = 5000;
 
 SELECT 
  Count(*) 
FROM 
  meerkat.property 
WHERE 
  (CASE WHEN @CountryId IS NOT NULL THEN CountryId = @CountryId ELSE 1 END)
  AND (CASE WHEN  @CityId IS NOT NULL THEN CityId = @CityId ELSE 1 END) 
  AND (CASE WHEN @CategoryId IS NOT NULL THEN CategoryId = @CategoryId ELSE 1 END) 
  AND (CASE WHEN  @StateProvince IS NOT NULL THEN StateProvince = @StateProvince ELSE 1 END) 
  AND (CASE WHEN @TransactionTypeId IS NOT NULL THEN TransactionTypeId = @TransactionTypeId ELSE 1 END) 
  AND (CASE WHEN @Price IS NOT NULL THEN Price <= @Price ELSE 1 END) 
  AND IsPublic = 1 
  AND IsBlocked = 0;

Thanks in advance

CodePudding user response：

If no when conditions are met, case returns null. If you want each test to pass, you need to return a true value instead, so:

case when @price is not null then
          price < @price
    else 1 end
and ...