CODEWITHSUNDEEP
mysqlstored-procedurescursordelimiter
ALEXANDER GOH
ALEXANDER GOH

Reputation: 23

Syntax Error for using cursor in a stored procedures

I searched the whole website for answer but none of it solve my problem so I'm gona post my error with a new thread.

My code,

DELIMITER $$

USE `DB`$$

DROP PROCEDURE IF EXISTS `GET_Summary`$$

CREATE DEFINER=`connect`@`%` PROCEDURE `GET_Summary`(IN pDateFrom DATE,IN pDateTo DATE)

ROOT:BEGIN

    DECLARE pTotal,pShortCode,pSignUp,pUnSub,pJunk INT;
    DECLARE pCOM NVARCHAR(10);
    DECLARE no_more_rows BOOLEAN;

    CREATE TEMPORARY TABLE TMOMain
    (GrandTotal INT,ShortCode INT,COM NVARCHAR(10),SignUp INT,UnSub INT, Junk INT);  

    DECLARE MoMainCur CURSOR FOR
    SELECT COUNT(*),shortcode,(CASE WHEN ComID= 1 THEN 'A'
    WHEN ComID= 2 THEN 'B' WHEN ComID= 3 THEN 'C' ELSE 'UV' END) AS COM
    FROM tbl_inbox 
    INNER JOIN tbl_keyword ON keywordid = recvkeyword 
    WHERE recvDate >='2011-11-15'  AND recvDate < '2011-11-16' 
    GROUP BY shortcode,COM;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;    

    OPEN MoMainCur;

    read_loop:LOOP
    FETCH MoMainCur INTO pTotal,pShortCode,pCOM ;

    INSERT INTO TMOMain
    VALUES
    (pTotal,pShortcode,pCOM ,0,0,0);
    IF no_more_rows THEN
        CLOSE MoMainCur;
        LEAVE the_loop;

    END LOOP;

    -- CLOSE MoMain;

    SELECT * FROM TMOMain;
    END$$
DELIMITER ;

MYSQL version 5.1

ERROR Message:

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 'DECLARE MoMainCur CURSOR FOR

Upvotes: 1

Views: 1225

Answers (1)

mu is too short
mu is too short

Reputation: 434635

From the fine manual:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Declarations must follow a certain order. Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring cursors or handlers.

Emphasis mine.

Move your CREATE TEMPORARY TABLE TMOMain to after all your DECLAREs.

Upvotes: 2

Related Questions