Bug #17710 DECLARE HANDLER in IF block causes syntax error ?

• Submitted: 24 Feb 2006 20:15
• Modified: 25 Feb 2006 22:12
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.18
• OS: Windows (Windows Server 2003)
• CPU Architecture: Any

[24 Feb 2006 20:15] [ name withheld ]

Description:
DECLARE HANDLER when put into IF block throws out syntax error. The workaround is to encapsulate it inside begin end block, all put into IF block.

How to repeat:
CREATE PROCEDURE `test1`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	IF TRUE THEN
		DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT false;
	END IF;
END;

Suggested fix:
Just a workaround which does what it's expected to do and compiles well...

CREATE PROCEDURE `test1`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	IF TRUE THEN
		block:BEGIN
			DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT false;
		END block;
	END IF;
END;

[24 Feb 2006 20:17] [ name withheld ]

it's stored procedures category actually

[25 Feb 2006 22:12] Valeriy Kravchuk

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/declare.html):

"The DECLARE statement is used to define various items local to a routine:
- Local variables. See Section 17.2.7, “Variables in Stored Routines”.
- Conditions and handlers. See Section 17.2.8, “Conditions and Handlers”.
- Cursors. See Section 17.2.9, “Cursors”.

The SIGNAL and RESIGNAL statements are not currently supported.

DECLARE is allowed only inside a BEGIN ... END compound statement and must be at its start, before any other statements."

So, it is not a bug, but intended and documented behaviour.