1

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;
shA.t
  • 16,580
  • 5
  • 54
  • 111
Tom Melly
  • 98
  • 7

1 Answers1

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Re: `like_p_person`, I was just wondering the same thing. It is not in the official MySQL documentation, and I can't even think of what function such syntax would serve. – Uueerdo May 11 '15 at 16:06
  • Apologies - that was copied from a create statement, but I thought an 'if exists' would be clearer than an 'if not exists'... and I made things even more confusing... SOML – Tom Melly May 11 '15 at 16:10
  • Also, does that let me distinguish between SQL with and without the 'if [not] exists' ? Mysql knows the difference - it uses a different coloured icon in Query Browser depending on whether the 'if exists' was there or not - but I'm damned if I can distinguish between them. – Tom Melly May 11 '15 at 16:11
  • TomMelly: `IF NOT EXISTS` isn't valid in a `drop table` statement. That would throw MySQL error 1064. To catch a MySQL error in a handler, you need to specify the MySQL error number or the corresponding SQLSTATE to be caught. (You could specify a named condition, but the condition has to resolve to a MySQL error number or SQLSTATE). Reference: [https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html](https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html) – spencer7593 May 11 '15 at 16:15
  • Apologies (again). I've complicated matters by not checking the supplied code. Should I replace the text of the original post, or does that just confuse things? – Tom Melly May 11 '15 at 16:20
  • TomMelly: Its fine to edit your question if those edits clarify your *original* question, by providing additional context and details. That's fine. But it's not okay to *change* the question to an entirely different question. I suspect you're asking about `CREATE TEMPORARY TABLE IF NOT EXISTS`. If the table already exists, that statement would throw MySQL error 1050. Declare a separate handler for that error. (I've added to my original answer.) – spencer7593 May 11 '15 at 16:27