Mysql drop table if exists inside a procedure

Question

I am trying to apply an example nested set model with procedures. I found many of them with this technique, and in the process I found a problem. Every time I call a procedure, I get an unknown table XXX . When I create a procedure, I have no problem at all. Quick example:

 CREATE PROCEDURE `sp_getRoleTree` (IN root INT) READS SQL DATA BEGIN DECLARE rows SMALLINT DEFAULT 0; DROP TABLE IF EXISTS ROLE_TREE; CREATE TABLE ROLE_TREE ( nodeID INT PRIMARY KEY ) ENGINE=HEAP; INSERT INTO ROLE_TREE VALUES (root); SELECT * FROM ROLE_TREE; DROP TABLE ROLE_TREE; END; 

So my question is: I'm doing something wrong here (this is sample code), can I turn off the warning about whether the code exists, if it is ok? Is there a special loop inside procedures that trigger such warnings?

Answer 1

How it works: try cropping the table instead of re-creating it.

Do not use DROP TABLE / CREATE TABLE . Create this table once (or when you need it) and use the TRUNCATE TABLE command.

Answer 2

MySQL generates a warning when using DROP TABLE IF EXISTS tbl; when the table does not exist. This can be confusing and possibly counter intuitive, but this is the expected behavior.

From http://dev.mysql.com/doc/refman/5.5/en/drop-table.html

Use IF EXISTS to prevent errors from occurring for tables that do not exist. When using IF EXISTS, a NOTE is created for each non-existent table. See Section 13.7.5.41, SHOW WARNINGS Syntax .

IF EXISTS does not allow MySQL to throw an error, which is nice, but it raises a warning if the table does not exist. Cannot suppress this warning.