Mysql – Errors declaring variables in stored procedure

MySQLstored-procedures

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this.

I don't know where the error is because when I switch the how_many section after the _rollback, The error changes.

What's wrong with the way I'm declaring variables here?

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
    DECLARE how_many INT;
    SELECT COUNT(*) FROM things INTO how_many;
    -- Prepare roleback.
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    -- Start transaction.
    START TRANSACTION;
    -- Do all the things.
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF; 
END $$
DELIMITER ;

Best Answer

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. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

Try this :

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
       -- DECLARE STATEMENTS first
        DECLARE how_many INT;
        DECLARE `_rollback` BOOL DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
        --
        SELECT COUNT(*) INTO how_many FROM things;       

        -- Start transaction.
        START TRANSACTION;
                -- Do all the things.
        IF `_rollback` THEN
                ROLLBACK;
        ELSE
                COMMIT;
        END IF; 
END $$
DELIMITER ;
Related Question