Update statement in stored procedure is not working

Question

I have a stored procedure in Mysql database like this:

DELIMITER $$

USE `vboard_75`$$

DROP PROCEDURE IF EXISTS `sp_LongWaitCall`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LongWaitCall`()

BEGIN


    UPDATE cdr SET cdr.CallStatus='DISCONNECTED',cdr.EndTime=NOW() WHERE cdr.CallStatus='RINGINGIN' 
    AND MINUTE(DATEDIFF(cdr.StartTime,NOW())) >=7;

    DECLARE _StatVal FLOAT;
    DECLARE _DevID INT;
    DECLARE Record_Fetch INT DEFAULT 0;
    DECLARE crsr_Board CURSOR FOR
    SELECT  IFNULL(MAX(SECOND(DATEDIFF(CDR.StartTime, IFNULL(CDR.EndTime, NOW())))), 0) AS LRT,vw_Boards_Ext.boardid
    FROM    vw_Boards_Ext RIGHT OUTER JOIN
                boards ON vw_Boards_Ext.boardid = boards.boardid LEFT OUTER JOIN
                CDR ON vw_Boards_Ext.Ext = CDR.DDI AND 
                STR_TO_DATE(CONCAT(boards.ResetDate,' ',boards.ResetTime),'%m/%d/%Y %H:%i') < CDR.timestamp
    AND CDR.CallStatus='RINGINGIN'
    GROUP BY vw_Boards_Ext.boardid;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET Record_Fetch = 1;
    OPEN crsr_Board;
    FETCH crsr_Board INTO _StatVal, _DevID;
    WHILE Record_Fetch = 0 DO

        UPDATE stat_values AS sv SET sv.StatValue = _StatVal,sv.timestamp= NOW() 
        WHERE sv.itemId = _DevID AND sv.itemType = 'boards' AND sv.StatId = 3;
        FETCH crsr_Board INTO _StatVal, _DevID;

    END WHILE;
    CLOSE crsr_Board;
    DEALLOCATE PREPARE crsr_Board;
END$$

DELIMITER ;

Error:

Query: CREATE DEFINER=root@localhost PROCEDURE sp_LongWaitCall() BEGIN update cdr set cdr.CallStatus='DISCONNECTED',cdr.EndTime=n...

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 'DECLARE _StatVal FLOAT; DECLARE _DevID INT; DECLARE Record_Fetch INT DEFAULT 0' at line 8

Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.001 sec

when i delete update statement then its working fine – Abid Hussain Jan 27 '17 at 06:42 — Abid Hussain, Jan 27 '17 at 06:42
https://dev.mysql.com/doc/refman/5.7/en/declare.html – Ravinder Reddy Jan 27 '17 at 06:59 — Ravinder Reddy, Jan 27 '17 at 06:59

score 0 · Answer 1 · edited May 23 '17 at 12:16

This documentation refers to having the DECLARE statements at the top and also before the creation of any CURSORS and HANDLERS, which you are following but not at the beginning of the BEGIN... END block.

This documentation clarifies the question that OP had posted!

It is mandatory to have your declarations at the begin of your BEGIN... END block, but not under an UPDATE statement.

Additional reference to a similar issue can be found on this question, even which refers to the same solution.

Hope this helps!

Update statement in stored procedure is not working

1 Answers1