0

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...

1
  • why use a sproc at all in this case? Aug 14, 2016 at 1:02

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.