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!