Declare error handler not valid in MySQL?

Question

I'm trying to write my first stored procedure that uses an error handler, but It doesn't seem to accept my syntax. Here's what I'm using:

DELIMITER //

CREATE PROCEDURE min_list_valid (OUT pass CHAR
                                ,OUT errs VARCHAR(50)
                                ,IN `@list_id` INT
                                ,IN `@min_input` INT)
BEGIN
    BEGIN
        DROP TABLE IF EXISTS `errors_table`;
        CREATE TEMPORARY TABLE `errors_table`(error VARCHAR(30))ENGINE=MEMORY;
    END;

    BEGIN
        DECLARE error_holder VARCHAR(30);
        DECLARE error_checker VARCHAR(30);
        SET error_checker = 0;

        DECLARE CONTINUE HANDLER FOR SQLSTATE, SQLEXCEPTION
        BEGIN
            SELECT 'The user input is less than the minimum for this list' INTO error_holder;
            SELECT error_holder INTO `errors_table`;
            SET error_checker = 1;
        END;

        SELECT      IF(MAX(CAST(`peg_num` AS UNSIGNED))<`@min_input`,'pass',NULL) AS `key`
        FROM        `game_lists`
        WHERE       `list_id` = `@list_id`
        INTO        pass;

        IF error_checker <> 0 THEN
            SELECT * FROM `errors_table`
        END IF;
    END;
END; //

It gives me back the error message:

#1064 - 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 CONTINUE HANDLER FOR SQLSTATE, SQLEXCEPTION BEGIN ' at line 16

But that's strange, it's telling me the declare is wrong but the line in error is line 16 (the one with SET error_checker = 0). I believe I'm following the correct syntax so... what's wrong?

What this stored proc just does is select some maximum value from a list, and everything else is just me trying to create an error handler and an error table to store the generated errors. I wanted to try a simple functionality first with the "full blown" error handling so there's not too many debug points.

My MySQL version is 5.6.14 - MySQL Community Server (GPL)

Solution

Your corrected procedure should look like below. Few mistakes as pointed below

DELIMITER //
CREATE PROCEDURE min_list_valid (OUT pass CHAR
                                ,OUT errs VARCHAR(50)
                                ,IN `@list_id` INT
                                ,IN `@min_input` INT)
BEGIN
        DECLARE error_holder VARCHAR(30); <--Move this declare statement above here
        DECLARE error_checker VARCHAR(30);
        SET error_checker = 0;
    BEGIN
        DROP TABLE IF EXISTS `errors_table`;
        CREATE TEMPORARY TABLE `errors_table`(`error` VARCHAR(30))ENGINE=MEMORY; 

END;

BEGIN <-- after begin you should declare only the error handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000', SQLEXCEPTION
                                            <-- Provide a valid SQL State                                
    BEGIN
        SELECT 'The user input is less than the minimum for this list' INTO error_holder;
        INSERT INTO `errors_table`(`error`) SELECT error_holder;
        SET error_checker = 1;
    END;

    SELECT      CASE WHEN MAX(CAST(`peg_num` AS UNSIGNED)) < @min_input THEN 'pass'
                ELSE NULL END AS `key`
    FROM        `game_lists`
    WHERE       `list_id` = `@list_id`
    INTO        pass;

    IF error_checker <> 0 THEN
        SELECT * FROM `errors_table`;
    END IF;
END;
END; //

Comments

[markovchain]

Thanks! I see, of course, declaring should always be at the top. I'm running this now though and it asks me to declare the "errors_table" variable, even though I'm creating a temporary table. How would I go about declaring this?

[Rahul]

@markovchain, there was few more mistakes made by you. Check edit. it will be fine now.

[markovchain]

Nice! Thanks! It's great I now have a base SP to refer to and analyze. Thanks again! :)