I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically.
I have never done anything similar, so I read the appropriate documentation and studied some examples. My code is the following:
DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);
SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM
(SELECT current_Price as price FROM UNION_SALES WHERE location = ', loc,' and current_Price >5000
ORDER BY current_Price ASC LIMIT ?) as prices;');
PREPARE STMT FROM @quer;
EXECUTE STMT USING @a;
END $$
DELIMITER ;
The error I get when I call the procedure is:
CALL lower10_avg_price('Αρχαία Αγορά ');
Error Code: 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 'Αγορά and current_Price >5000 ORDER BY current_Price ASC LIMIT ' at line 3
Can someone please help me understand what is causing this error? Thank you for any suggestions!