Help! Unable to declare EXIT HANDLER in stored proc!

[someguy321]

I'm using MySQL 5.1.41-3 (with phpMyAdmin) and the below stored procedure throws a syntax error about the "DECLARE EXIT HANDLER" line. If I remove that, it goes through fine.

 

(NOTE: I know this is a bad proc, it's just for testing)

 

(I set the delimiter to //)

DROP PROCEDURE `spTest`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `spTest`
(
    IN _name varchar(255), 
    IN _age int 
)
BEGIN
    DECLARE statusCode int;
    DECLARE statusMessage varchar(255);
    
    SET statusCode = 0;
    SET statusMessage = 'OK';
    
    #THIS THROWS SYNTAX ERROR!
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT status as -1, statusMessage as "Unknown Error Occurred.";
        call cleanup( "exception handler called in spTest");
    END;
    
    IF (SELECT name FROM Test WHERE name = _name) IS NOT NULL THEN
        SET statusMessage = "name already exists";
        SET statusCode = 1;
        
    ELSEIF (SELECT age FROM Test WHERE age = _age) IS NOT NULL THEN
        SET statusMessage = "age already exists";
        SET statusCode = 2; 
        
    ELSE
        INSERT INTO Test (name, age) VALUES(_name, _age);
    END IF;
    
    SELECT statusCode as "status", statusMessage as "message";
END //

[someguy321]

Anyone have any suggestions?

[fenway]

You know, if you're going to post your question elsewhere, at least have the courtesy to post the solution when you find it.

[someguy321]

You know, if you're going to post your question elsewhere, at least have the courtesy to post the solution when you find it.

 

Yep, sorry about that!

 

For anyone facing this issue:

 

In any block, DECLARE must precede all other code.