Pass array to MySQL stored routine?

We need to create a stored procedure to display how to pass array to MySQL stored routine. Let us first create a table for our example.

Creating a table

mysql> create table FindDemo
   -> (
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.46 sec)

Inserting some records into the table.

mysql> insert into FindDemo values('John'),('Smith');
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from FindDemo;

The following is the output.

+-------+
| name  |
+-------+
| John  |
| Smith |
+-------+
2 rows in set (0.00 sec)

To create a stored routine that accepts an array as a parameter.

mysql> delimiter //
mysql>CREATE  PROCEDURE SearchingStoredProcedure(IN ArrayDemo VARCHAR(100))
   -> BEGIN
   ->     SELECT * FROM FindDemo
   ->     WHERE FIND_IN_SET(name, ArrayDemo);
   ->
   -> END//
Query OK, 0 rows affected (0.14 sec)

Passing array as a parameter.

mysql> delimiter ;
mysql> call SearchingStoredProcedure('David,Bob,John');

Here is the output.

+------+
| name |
+------+
| John |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Arjun Thakur

e

Updated on: 30-Jul-2019

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started