MySQL stored procedure - distinguish between note and error

Question

I have a stored procedure that executes stored SQL.

However, the error-handler kicks-in and exits if the user attempts to execute

drop temporary table if exists t_person;

and 't_person' doesn't exist. I'm perfectly happy to generate an error when 'if exists' is not given, but how do I avoid an error for the earlier case (the error-code is unchanged)?

Here's my error handler:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  set @sql = 'select \'Invalid SQL or bad parameter resulting in invalid SQL\' as `Error`';
  prepare stmt from @sql;
  execute stmt;
END;

Answer 1

You could use a CONTINUE handler rather an an EXIT handler that catches MySQL error 1051 "Unknown table"...

DECLARE CONTINUE HANDLER FOR 1051 BEGIN END;

-or-

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;

EDIT

To catch a MySQL error in an exception handler, you need to specify the MySQL error number or the corresponding SQLSTATE to be caught. (You could specify a named condition, but that named condition has to resolve to a MySQL error number or SQLSTATE).

A syntax error would throw MySQL error 1064.

If a table foo exists, and you issue a

CREATE TEMPORARY TABLE IF NOT EXISTS `foo` (id INT);

That would throw MySQL error 1050.

To catch that error, declare another handler for that. Assuming you want to "swallow" the exception and continue processing...

DECLARE CONTINUE HANDLER FOR 1050 BEGIN END;

Reference: https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

---

The like p_person in the drop temporary table statement looks wrong to me; at least, I'm not familiar with using the LIKE keyword in a DROP TABLE statement.