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?