vlio20 vlio20 - 1 year ago 114
MySQL Question

Passing an integer array to mysql procedure

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
but it did exactly the same as

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

Answer Source

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


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download