MySQL Forums
Forum List  »  Stored Procedures

Error handler only works for first error
Posted by: jmrSudbury
Date: April 18, 2005 01:39PM

I found another one. I am trying to use the error handler from the hierarchy stored procedure in the mysql-storedprocedures.pdf ( http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html ). I want to delete 2 temporary tables at the beginning of my procedure. I named the second one differently just in case it only takes the first 6 characters. Not even that helped. When I run the following:

Delimiter $
drop procedure if exists sp_populatebudget\g
create procedure sp_populatebudget()
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
DROP TABLE IF EXISTS tmp_BudgetUpdate5;
END;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
DROP TABLE IF EXISTS tmp_8BudgetUpdate8;
END;
END$
call sp_populatebudget()\g

I get this error:

ERROR 1146 (42S02): Table 'gl.tmp_8BudgetUpdate8' doesn't exist

I ran these commands back to back without a problem at the mysql> prompt:
DROP TABLE IF EXISTS tmp_BudgetUpdate5;\g
DROP TABLE IF EXISTS tmp_8BudgetUpdate8;\g

Am I doing something wrong?

I originally tried this, but created the extra begin-end sub block just in case it would help:

create procedure sp_populatebudget()
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
DROP TABLE IF EXISTS tmp_BudgetUpdate5;
DROP TABLE IF EXISTS tmp_8BudgetUpdate8;
END;
END$

Does anyone have any idea if there is a workaround for this? Or will I only be able to use a single temporary table or permanent tables throughout the entire procedure?

Options: ReplyQuote


Subject
Views
Written By
Posted
Error handler only works for first error
4576
April 18, 2005 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.