Variable-length argument for query IN() clause

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:



DROP PROCEDURE IF EXISTS passInParam;

DELIMITER |

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

BEGIN

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

  PREPARE stmt FROM @qry;

  EXECUTE stmt;

  DROP PREPARE stmt;

END;

|

DELIMITER ;

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''' ));