how to use IN() function inside of stored procedure?

1
votes

I have using mysql with stored procedure. Inside of strored procedure i have write the select query with IN() function. I have pass the 5 value inside of the function but it display the first value data's only.my code is

DELIMITER $$ CREATE DEFINER=root@localhost PROCEDURE pro1(IN reg_id VARCHAR(50))

BEGIN

SELECT COUNT(master_country_id) AS num FROM master_country WHERE master_region_id IN(reg_id) ORDER BY master_country_id DESC;

END$$

DELIMITER ;

the countries are,

1 - India

2 - Australia

3 - Canada

4 - Spain

5 - Srilanka

I have call the procedure is

CALL pro1(1,4,2);

The actual Result is,

India,Spain,Australia

But It display the result is,

India

2
mysqlstored-procedures
how many parameters are there? ONE, how many in your result? ONE. Just because that varchar(50) string LOOKS like 3 values, it is just ONE VALUE that contains commas. - Paul Maxwell
See if this answer makes sense to you stackoverflow.com/questions/25839148/… - Paul Maxwell

2 Answers

1
votes
DELIMITER $$ CREATE DEFINER=root@localhost PROCEDURE pro1(reg_id VARCHAR(50))

BEGIN

SELECT COUNT(master_country_id) AS num FROM master_country WHERE master_region_id REGEXP (reg_id) ORDER BY master_country_id DESC;

END$$

DELIMITER ;

you can call your proc as CALL pro1('1|4|2');

0
votes

You cannot quite use in the way that you want. The way your code is phrased, the stored procedure only finds a match when a master region matches a full string. So, if you pass in 1,2,3, then the match is on exactly that 1,2,3 -- not on each element.

You can get what you want using find_in_set():

BEGIN

SELECT COUNT(master_country_id) AS num
FROM master_country
WHERE find_in_set(reg_id, master_region_id) > 0
ORDER BY master_country_id DESC;

END$$

This works, but it cannot make use of an index.

You could use dynamic SQL (prepare/exec) as well, but that incurs overhead on compiling the query.