Variable-length argument for query IN() clause

from the Artful Common Queries page

To have an sproc accept a variable-length parameter list for an IN(...) clause in a query, code the sproc to PREPARE the query statement:



CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )


  SET @qry = CONCAT( qry, param, ')' );

  PREPARE stmt FROM @qry;

  EXECUTE stmt;





For this example, the query string should be of the form:

SELECT ... FROM ... WHERE ... IN ( 

but so long as it has those elements, it can be as complex as you like. When you call the sproc:
1. Quote each argument with a pair of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole param string with another set of single quotes:

CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (', ('''abc'',''def'',''ghi''' ));

Return to the Artful Common Queries page