Error when passing multiple values

Question

I have below stored procedure..

it only works when I pass single value like "BH" but not when passing multiple values like "AE", "BH", "US", "FR"

CALL sp_populate_memo_country_companies("BH")

returns 5 rows

CALL sp_populate_memo_country_companies("BH", "US")

throw error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '")' at line 1

here is the stored procedure:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_populate_memo_country_companies`(IN param_country varchar(255))
BEGIN
 SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
 FROM locations
 JOIN country ON country.country_code_alpha2 = locations.country_code
 JOIN companies ON companies.company_id = locations.company_id
 LEFT JOIN payroll ON payroll.location_id = locations.location_id
 WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
 GROUP BY locations.location_id
 ORDER BY companies.company_name;
END

Kindly help...

• Neil McGuigan: why use a sproc at all in this case?

Answer 1

This is one (of many) situation where Stored Routines are less useful than one would like.

A possible workaround: Build a VIEW without the IN clause, then use the IN clause when fetching from the VIEW.