Re: problem with DECLARE CONTINUE HANDLER

[Peter Brawley]

Here's an example that works ...;

USE test ;
DROP TABLE IF EXISTS t;
CREATE TABLE t(i INT PRIMARY KEY, j INT) ;

DROP PROCEDURE IF EXISTS errhandlerdemo ;
DELIMITER go
CREATE PROCEDURE errhandlerdemo( pvalue INT, INOUT pResult VARCHAR(64) )
BEGIN
  BEGIN 
    DECLARE bad_key CONDITION FOR SQLSTATE '23000';
    DECLARE EXIT HANDLER FOR bad_key
    BEGIN
      SET pResult=CONCAT(pResult,'Insert failed; invalid key. ');
    END;
      SET pResult='Block 1 executed. ';
    INSERT INTO t VALUES( pvalue, NULL ) ;
    SET pResult=CONCAT(pResult,'Continuing after insert. ');
  END;
  SET pResult=CONCAT(pResult, 'Block 2 executed.');
END ;
go
DELIMITER ;
SET @result = NULL; CALL errhandlerdemo(NULL,@result);
SELECT @result;