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

Return to the Artful Common Queries page