Re: How to pass varchar as WHERE field IN param
...in(param_country)... will compare column values with the param as a single value. Not what you had in mind.
If param_country is a comma-separated list, you need to form the query by concatenating ...
"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;"
into one query string. To do that, a stored procedure needs to PREPARE the statement, so you may as well form the query string in C# code and execute it directly. Simpler and quicker.
There's another problem. A query like ...
select a,b,c,d ... group by a ...
... will return arbitrary results for b,c,d unless there is no more than one b,c or d value for each value of a. That is, your row selection is blind. Is that really the desired result?