CODEWITHSUNDEEP
mysqlsqlstored-procedures
Guilherme Oderdenge
Guilherme Oderdenge

Reputation: 5001

If isn't null, use "where"

The goal

Use the clause "where" only if parameter is not null.

The problem

I do not know the syntax.

What I have

The follow syntax that doesn't 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 at line 19.

Duplicate question?

I don't think so. I search about this subject, but without sucess — then I came here to post.

Details

I read about Control Flow Functions here, but it is still confusing to me.

Thanks in advance!

Upvotes: 4

Views: 104

Answers (3)

RandomSeed
RandomSeed

Reputation: 29759

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

Or (for educational purpose only):

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

But do not do this...

For information, the form you used is the "Control Flow statement", meant for controlling the execution flow in a procedure. The form I proposed (in the second not-to-be-used option) is a "Control Flow function" (essentially, a function call).

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

Upvotes: 0

Karoly Horvath
Karoly Horvath

Reputation: 96258

So you want to get record which match that predicate if a field is not NULL. That's like saying get them if the field is NULL, otherwise filter. Simply combine the two predicates with OR:

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

Upvotes: 3

Steph Locke
Steph Locke

Reputation: 6146

How about a coalesce?

WHERE `map`.`Product_State` = inOffer

  AND `ca`.`Category_Id` = coalesce(categoryId,`ca`.`Category_Id`)

Upvotes: 1

Related Questions