MySQL Forums
Forum List  »  Stored Procedures

Re: Error when passing multiple values
Posted by: Peter Brawley
Date: August 12, 2016 07:00PM

Looks like the question you asked a week ago. The same answers still apply. Assuming param_country is a string consisting of a list of comma-separated, quoted values, you cannot directly insert that list into the Select command. you need to form the query this way:

set @qry = 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 @qry;
Execute stmt;
Drop Prepare stmt;

If it doesn't work the first tine, have the sproc run ...

Select @qry;

so you can discern the problem.

And again, if you're already writing in an API language, you can directly concatenate query parts in that language before sending the query to MySQL, so putting this query in an sproc just makes the job more difficult.

Options: ReplyQuote


Subject
Views
Written By
Posted
3086
August 12, 2016 04:52PM
Re: Error when passing multiple values
390
August 12, 2016 07:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.