CODEWITHSUNDEEP
mysqlsqlstored-procedures
Alexander Kleinhans
Alexander Kleinhans

Reputation: 6248

Errors declaring variables in stored procedure?

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 ;

EDIT:

when I declare variables like this:

    DECLARE how_many INT;
    -- Prepare roleback.
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    SELECT COUNT(*) FROM tiers INTO how_many;

It seems to be fine. Am I not allowed to declare more variables after certain variables in a procedure are set?

Upvotes: 1

Views: 2519

Answers (2)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

The DECLARE statements are only permitted after BEGIN, if you try to add another DECLARE after that it shows error.

Move the line SELECT COUNT(*) FROM things INTO how_many; after all DECLARE lines.

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

                    -- Do all the things.
            IF `_rollback` THEN
                    ROLLBACK;
            ELSE
                    COMMIT;
            END IF; 
    END $$
    DELIMITER ;

Upvotes: 2

Drew
Drew

Reputation: 24949

From the Manual Page DECLARE Syntax:

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.

Note that these are called Local Variables in mysql. These differ from User Variables (prefixed with the @ symbol), which can be sprinkled about with your statements and do not require a DECLARE.

Upvotes: 2

Related Questions