If the value is not zero, use "where",

Question

goal

Use the where clause only if the parameter is non-zero.

Problem

I do not know the syntax.

What i have

The following syntax does not work.

CREATE DEFINER=`root`@`localhost` PROCEDURE `getProductsListForHome` (IN `inOffer` INT, IN `categoryId` INT) BEGIN SELECT (MIN(`map`.`Product_Price`)) as `minProductPrice`, (MAX(`map`.`Product_Price`)) as `maxProductPrice`, `pr`.`Product_Name` as `productName`, `ca`.`Category_Name` as `categoryName` FROM `bm_market_products` as `map` JOIN `bm_products` as `pr` ON `map`.`Product_Id` = `pr`.`Product_Id` JOIN `bm_products_category_relationship` as `car` ON `pr`.`Product_Id` = `car`.`Product_Id` JOIN `bm_product_categories` as `ca` ON `car`.`Category_Id` = `ca`.`Category_Id` WHERE `map`.`Product_State` = inOffer IF (`categoryId` != null) THEN AND `ca`.`Category_Id` = `categoryId` END IF; GROUP BY `map`.`Product_Id`; END 

The problem is line 19.

Duplicate question?

I do not think so. I am looking for about this subject, but without success - then I came here to publish.

More details

I read about control flow functions here , but it still confuses me.

Thanks in advance!

Answer 1

So, you want to get a record that matches this predicate if the field is not NULL. This, how to say, get them if the field is NULL, otherwise a filter. Just combine the two predicates with OR :

 AND (`categoryId` IS NULL OR `ca`.`Category_Id` = `categoryId`) 

Answer 2

How about a merger?

 WHERE `map`.`Product_State` = inOffer AND `ca`.`Category_Id` = coalesce(categoryId,`ca`.`Category_Id`) 

Answer 3

 WHERE ... AND (ca.Category_Id = categoryId OR categoryId IS NULL) ... 

Or (for educational purposes only):

 WHERE ... AND IF(categoryId IS NULL, TRUE, ca.Category_Id = categoryId) 

But do not do this ...

For information, the stream operator form is used. ", designed to control the flow of execution in the procedure. The form that I proposed (in the second optional option) is a function of the control flow "(essentially a function call).

The first form is the same as in all imperative languages. The second option is for use in an SQL statement. The latter should be used only as a last resort, because there is almost always a better way to write a query.