0

How to write the correct WHERE clause in PROCEDURE which depends on the parameters come in arrays and their default value is NULL

my attempt


DELIMITER //
CREATE PROCEDURE `task`(IN param1 TEXT, IN param2 TEXT)

BEGIN
  SELECT *
  FROM customer
    LEFT JOIN items 
       ON customer.id = items .id
  WHERE
    CASE
      WHEN param1 is NULL OR param1  = "" then " " else id IN (param1)
    END
    CASE
      WHEN param2 is NULL OR param2  = "" then " " else name IN (param2)
    END


END//

WHERE clause should check if each parameter is null and if so, go to the next parameter, and if not, it should execute the formula f.e.q `name` IN (param2) and then go to check next param

I want to get result like from this query

  SELECT *
  FROM customer
    LEFT JOIN items 
       ON customer.id = items .id
  WHERE 1=1
  " . (($this->params["param1"]==array())?"" : "AND id IN (:param1)") . "
  " . (($this->params["param2"]==array())?"" : "AND item IN (:param2)") . "
5
  • Your CASEs makes no sense.
    – Akina
    Jun 12 at 19:07
  • I wouldn't write the post if it made sense @Akina
    – Hub
    Jun 12 at 20:51
  • I don't think anyone can give an answer unless you describe what this query is intended to do. As it is currently shown, it does nothing (even if one were to correct the syntax error). There's no way anyone can guess what it is supposed to do. Jun 12 at 22:47
  • @BillKarwin I have updated the post and I hope it is now more clear.
    – Hub
    Jun 13 at 5:45
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jun 13 at 8:27

1 Answer 1

0

I suggest this alternative query:

SELECT *
FROM customer
LEFT OUTER JOIN items ON customer.id = items .id
WHERE FIND_IN_SET(id, param1) OR FIND_IN_SET(name, param2)

Your input params are strings, not arrays. MySQL has no array type.

Read about the function FIND_IN_SET(). It searches a string that contains a comma-separated list.

Note that this query is hard to optimize. It will not use an index on id or name, so it's bound to do a table-scan. That's bad for performance if the table is large.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.