CODEWITHSUNDEEP
mysqlstored-procedures
rd22
rd22

Reputation: 1032

Call a stored procedure with an integer array as argument?

I want to pass an integer array as an argument to the stored procedure, how can this be done?

CREATE PROCEDURE rd22(unitList INT)
 BEGIN
    SELECT * FROM abet INNER JOIN a ON a.id = abet.alarm_source 
    AND a.unit IN (unitList) 
    WHERE abet.begin_timestamp = 1395874800000;
END

This is how the stored proc looks like, and I want to achive something like this:

SET @val = [1,2,3];
CALL rd22(@val);

One way I can think is pass a string and then somehow convert it to integers. Is there any other subtle way to achive this.

Thanks for your time.

Cheers.

Upvotes: 0

Views: 1406

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You cant do this way, when you pass the param as comma separated string in the query it will only take the first value while executing the in clause, you need to use dynamic query with prepared statement

CREATE PROCEDURE rd22(unitList varchar(100))
 BEGIN
    set @ul = unitList;
    set @qry = concat("select * from abet INNER JOIN a ON a.id = abet.alarm_source and a.unit IN(",@ul,") WHERE abet.begin_timestamp = 1395874800000");    
    prepare stmt from @qry;
    execute stmt;    
END

The call will be as

SET @val = '1,2,3';
CALL rd22(@val);

Here is a test case in mysql

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
|    7 | 2015-05-12 00:00:00 | 1970-01-01 00:00:00 | open     |
|    8 | 2015-05-12 00:00:00 | 1970-01-01 00:00:00 | new      |
|    9 | 2015-05-12 00:00:00 | 1970-01-01 00:00:00 | open     |
+------+---------------------+---------------------+----------+
9 rows in set (0.00 sec)


mysql> delimiter //
mysql> CREATE PROCEDURE testin(vals varchar(100))
    ->  BEGIN
    ->     set @ul = vals;
    ->     set @qry = concat("select * from test where id IN(",@ul,")");    
    ->     prepare stmt from @qry;
    ->     execute stmt;    
    -> END;//
Query OK, 0 rows affected (0.03 sec)

mysql> set @val = '3,4,6';
Query OK, 0 rows affected (0.00 sec)

mysql> call testin(@val);
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
3 rows in set (0.01 sec)

Upvotes: 1

Related Questions