Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    137

    Unanswered: Error when passing multiple values

    Hi,

    I have below stored procedure..

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

    when passing multiple values I get this error:

    Procedure execution failed
    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...

  2. #2
    Join Date
    Sep 2007
    Location
    Bangalore India
    Posts
    28
    Just change your query into prepare statement like below one.

    set @query = concat("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");

    prepare stmt from @query;
    execute stmt;

    call yourfunction('"AE", "BH", "US", "FR"')

    http://dev.mysql.com/doc/refman/5.7/...tatements.html

    let me know its working for you ?
    Last edited by benaya; 08-30-16 at 08:06. Reason: call function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •