BuySQL.com - Tools, software, tips of SQL

You can pass an array as a string, then build a query and use a prepared statements to execute it. In our example we pass a list of id values as parameter to the procedure.

Table creation:

CREATE TABLE languages(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY (language_id)
);
INSERT INTO languages VALUES 
  (1, 'English'),
  (5, 'Italian'),
  (10, 'Japanese'),
  (11, 'Mandarin'),
  (21, 'French'),
  (25, 'German'),
  (30, 'Spanish'),
  (32, 'Turkish');

Our procedure:

DELIMITER $$
CREATE PROCEDURE process_languages(IN list VARCHAR(255))
BEGIN
  SET @query = CONCAT('SELECT * FROM languages WHERE id IN (', list, ')');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

How to call this procedure: 

CALL process_languages('1,21,30');

Output:

+----+---------+
| id | name    |
+----+---------+
|  1 | English |
| 21 | French  |
| 30 | Spanish |
+----+---------+