Procedure with params

Question

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)") . "

Answer 1

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.