Passing an integer array to mysql procedure

Question

I want to create a stored procedure which receives array of integers and some other input, for example:

CREATE PROCEDURE test (field1 varchar(4), field2 varchar(4), array varchar (255))

and in the stored procedure I want to use it like this:

...
WHERE some_field IN (array)
...

The problem is that in this way I am getting only the rows which correspondence to the first integer in the array.

Is there any way to make it work (I also tried to use FIND_IN_SET but it did exactly the same as IN)?

The call that I am making for testing the stored procedure is CALL test (12, 13, '1, 2, 3').

Bill Karwin · Accepted Answer · 2014-04-07 17:56:18Z

4

FIND_IN_SET() works, but you can't have spaces in the string of numbers.

Demo:

mysql> select find_in_set(2, '1, 2, 3');
+---------------------------+
| find_in_set(2, '1, 2, 3') |
+---------------------------+
|                         0 |
+---------------------------+

mysql> select find_in_set(2, '1,2,3');
+-------------------------+
| find_in_set(2, '1,2,3') |
+-------------------------+
|                       2 |
+-------------------------+

So you should either form the list with no spaces before you pass it to your procedure, or else in the procedure, use REPLACE() to strip out spaces.

answered Apr 7, 2014 at 17:56

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2014-04-07 17:55:05Z

0

There no array concept. So what you can do probably is like

Your array variable has values '1, 2, 3' as string

split the string with , as delimiter which will
insert each value to a temporary table

so your temp table will have 1,2,3

Finally just use your temp table like

 WHERE some_field IN (select array_id from temp_table)

answered Apr 7, 2014 at 17:55

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Passing an integer array to mysql procedure

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related