Syntax Error for using cursor in a stored procedures

Question

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

mu is too short · Accepted Answer · 2011-11-19 05:32:53Z

2

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.

answered Nov 19, 2011 at 5:32

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ALEXANDER GOH Over a year ago

thanks! it solved the problem. 1st time using mysql, so I didn't really bother bout the order. thanks!

Collectives™ on Stack Overflow

Syntax Error for using cursor in a stored procedures

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related