Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Pass array to stored procedure

Pass array to stored procedure

Author: rootsql Date: 16-11-2011, 23:19

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 |
+----+---------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
This is a great post. It is really helpfull. Thanks alot.

Wow! i really love the commenting style. it's just awesome.
Information
Members of Guests cannot leave comments.