Errors declaring variables in stored procedure?

Question

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?

What is the error? I don't think you should have backticks around your procedure variable names. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 19, 2016 at 4:11
It just says there's an syntax exception. I actually got it working, but am not sure why. I'll edit my question. — Alexander Kleinhans
– Alexander Kleinhans, Commented Jun 19, 2016 at 4:11
You can't declare variables after you start code sections, meaning non-declares. That is my experience — Drew
– Drew, Commented Jun 19, 2016 at 4:51

Drew · Accepted Answer · 2016-06-19 05:00:56Z

3

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.

answered Jun 19, 2016 at 5:00

Drew

25k10 gold badges47 silver badges81 bronze badges

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

Comments

Ivan Cachicatari · Accepted Answer · 2016-06-20 21:36:09Z

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 ;

Collectives™ on Stack Overflow

Errors declaring variables in stored procedure?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related