Sample Header Ad - 728x90

Passing parameters in MySql procedure with prepare execute

0 votes
2 answers
994 views
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!
Asked by JDoe (1 rep)
Apr 12, 2021, 10:56 AM
Last activity: Apr 28, 2021, 06:03 AM