Bug #2672 Stored Procedure handler declaration causes syntax error

• Submitted: 7 Feb 2004 17:55
• Modified: 13 Feb 2004 12:32
• Reporter: Peter Gulutzan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.0-alpha-debug
• OS: Linux (SuSE 8.2)
• CPU Architecture: Any

[7 Feb 2004 17:55] Peter Gulutzan

Description:
Using  "DECLARE ... HANDLER FOR" inside a stored procedure causes a syntax error. 
This used to work. Now, even the example on the MYSQL Reference Manual "DECLARE 
Handlers" page http://www.mysql.com/doc/en/DECLARE_Handlers.html doesn't work. 
 

How to repeat:
mysql> delimiter | 
mysql> CREATE PROCEDURE handlerdemo () 
    ->     -> BEGIN 
    ->     ->   DECLARE CONTINUE HANDLER FOR '23000' SET @x2 = 1; 
    ->     ->   set @x = 1; 
    ->     ->   INSERT INTO test.t VALUES (1); 
    ->     ->   set @x = 2; 
    ->     ->   INSERT INTO test.t VALUES (1); 
    ->     ->   SET @x = 3; 
    ->     -> END; 
    ->     -> | 
ERROR 1064 (42000): 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 '-> BEGIN 
    ->   DECLARE CONTINUE HANDLER FOR '23000' SET @x2 = 1; 
    ->   set' at line 1 
 
... Or, more simply: 
 
CREATE PROCEDURE PY () BEGIN DECLARE CONTINUE HANDLER FOR '23000' SET 
@x = 1; END//

[8 Feb 2004 12:28] Peter Gulutzan

The error is in the documentation, not in the implementation. I have suggested a correction 
to the manual. So: the original example statement is "not a bug". 
 
However, there is still some DECLARE syntax that doesn't work. A corrected example: 
 
mysql> create procedure p1 () begin declare integrity_violation CONDITION FOR 
SQLSTATE '23000'; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p2 () begin declare integrity_violation CONDITION FOR 
SQLWARNING; end;// 
ERROR 1064 (42000): 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 'SQLWARNING; 
end' at line 1 
 
The same happens for SQLEXCEPTION, NOT FOUND. Integers are okay.

[13 Feb 2004 12:32] Peter Gulutzan

The whole complaint was really due to one page in the documentation. The documentation 
has now been fixed.