CODEWITHSUNDEEP
mysqlstored-procedures
godzilla
godzilla

Reputation: 3125

stored procedure error message

I have implemented the stored procedure as below, however i am getting the following error message when i try applying it:

ERROR 1064 (42000): 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 'WHERE symbol_id = id GROUP BY symbol_id;

I did some debugging and found that it was caused by the @max variable which i am trying to write the result into, however i do not see anything wrong with the syntax, can anyone please advise?

DROP PROCEDURE IF EXISTS `GENERATE_REPORT`;
DELIMITER $$
CREATE DEFINER=CURRENT_USER PROCEDURE `GENERATE_REPORT`()
BEGIN
    DECLARE id INT;
    DECLARE max INT;
    DECLARE at_end BIT DEFAULT 0;
    DECLARE cur CURSOR
        FOR SELECT symbol_id from trade;
        DECLARE CONTINUE HANDLER
        FOR SQLSTATE '02000' SET at_end=1;

    OPEN cur;
    FETCH cur INTO id;
    WHILE (NOT at_end) DO
        SELECT SUM(quantity) FROM trade INTO **@max** WHERE symbol_id = id GROUP BY symbol_id;
        FETCH cur into id;
    END WHILE;
    CLOSE cur;
END

$$ DELIMITER ;

Upvotes: 1

Views: 100

Answers (1)

Bohemian
Bohemian

Reputation: 425013

You have incorrect syntax in your SELECT ... INTO:

Change

SELECT SUM(quantity)
FROM trade 
INTO @max -- Incorrect placement
WHERE symbol_id = id
GROUP BY symbol_id;

To

SELECT SUM(quantity)
INTO @max -- Correct placement
FROM trade  
WHERE symbol_id = id
GROUP BY symbol_id;

The INTO should come right after the SELECT and before the FROM

Upvotes: 1

Related Questions