Does anyone see where the mistake can be? been dealing with this one all afternoon. Workbench isn't very useful in pointing out the mistakes.

It appears the next mistake in my code

CALL pCityMean('Spain') Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2.

DELIMITER $$
DROP PROCEDURE IF EXISTS pCityMean $$
CREATE PROCEDURE pCityMean (IN vPais VARCHAR(30))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE vID INT;
    DECLARE cur1 CURSOR FOR SELECT distinct id_city FROM world_temp_stats.temp_by_city where id_country=(SELECT id_country FROM world_temp_stats.country where Name=vPais);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur1;
         bucle1: LOOP
            FETCH cur1 INTO vID;
            IF done = 1 THEN
                LEAVE bucle1;
            END IF;
            SET @nomCiutat = (SELECT name FROM world_temp_stats.city WHERE id = vID); 
            SET @tablaCiutat = CONCAT ("SELECT year(dt), count(AverageTemperature), ROUND(avg(AverageTemperature), 2) INTO OUTFILE '",UCASE(vPais),"_",UCASE(@nomCiutat),"_temp_analisis.csv' FIELDS TERMINATED BY ';'
            LINES TERMINATED BY '\n' FROM world_temp_stats.temp_by_city where id_country=(SELECT id_country FROM world_temp_stats.country where Name='",vPais,"' and id_city=",vID," group by year(dt)");
            PREPARE execucio FROM @tablaCiutat;
            EXECUTE execucio;
            DEALLOCATE PREPARE execucio;
         END LOOP bucle1;
    CLOSE cur1;

END $$
DELIMITER ;

CALL pCityMean('Spain');

CodePudding user response：

I can eyeball your query and I see that you forgot a closing parenthesis.

SET @tablaCiutat = CONCAT ("SELECT ... 
  where id_country=(
    SELECT id_country FROM world_temp_stats.country 
    where Name='",vPais,"' and id_city=",vID," group by year(dt)
");

I think you got mixed up because year(dt) ends in a right-paren, but you need one more to close the subquery. Something like the following:

SET @tablaCiutat = CONCAT ("SELECT ... 
  where id_country=(
    SELECT id_country FROM world_temp_stats.country 
    where Name='",vPais,"' and id_city=",vID," group by year(dt)
  )
");

You should also use query parameters for dynamic values in your prepared query, instead of string-concatenation.

But if it were me, I would change so many things about this code. Why use INTO OUTFILE, and why use a stored procedure at all? In my experience, MySQL stored procedures are more difficult than writing the code in virtually any client programming language. I hardly ever use MySQL stored procedures.