Stack Exchange
Procedure with params
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)") . "


Your CASEs makes no sense.

I wouldn't write the post if it made sense @Akina

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.

@BillKarwin I have updated the post and I hope it is now more clear.

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.

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.

I create new post because I did't explaint what I mean. https://stackoverflow.com/questions/72631210/procedure-with-where-clause-and-parameters
@Bill-Karwin Thanks for help
