Stored procedure with select where in array

Question

I need Stored procedure for my select query.

My problem is i have String array it's contain multiple mobile numbers to compare db table if available or not.if available return row values.

I am using mysql 5.1.It's not supported for comparing array values in select query.

I got error Mysql feature not supported exception.so i will change my code using String builder. I already asked.

My question:[Mysql Stored procedure with java String Array

So if any possible to compare array values using Store procedure. My select query is :

SELECT USER_ID, USER_NAME, REGISTRATION_ID, IMEI_CODE, MOBILE_NUMBER FROM USER WHERE MOBILE_NUMBER IN (String_array);

In this query return if

`String_array = "one_string_values";

It return single rows.[Using java jdbc queryforObject]

If string_array = "array_of_values"`

It return nothing i got exception Mysql feature not supported exception

Any possibilities available for using Stored procedure in my scenario.

Answer 1

You basically want to pass multiple values to a Stored Procedure in MySQL. In SQL Server you can pass an XML chunk (as string) down to the SP, and then easily parse that XML extracting the multiple values from it in the SP body. In MySQL, I don't think this is easily possible. The best things you can do:

1) pass a comma-delimited string to the SP and parse that string it inside the SP;

2) generate the SQL dynamically:
MySQL stored procedure that accepts string with multiple parameters

3) you can also look at the FIND_IN_SET function;

4) you can also check the XML functions:
http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
and ExtractValue in particular.