mysql drop table if exists inside procedure
mysql, procedure, sql-drop
Solution
As a work around: try to truncate table instead of re-creating.
Do not use `DROP TABLE`/`CREATE TABLE`. Create this table once (or when you need it) and use `TRUNCATE TABLE` command.
Problem
I'm trying to apply a nested set model example with procedures. I've found many of them with this technique and in the process I've found a problem. Every time I call the procedure I get `unknown table XXX`. When I create the procedure I got no problem at all. The 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, am I doing something wrong here (it's example code), can I disable the warning on the if exists if the code is fine? Is there a special looping inside the procedures that's causing these kind of warnings?