mysql drop table if exists inside procedure

mysql procedure sql-drop
Alwin Kesler picture Alwin Kesler · Oct 29, 2012 · Viewed 11.1k times · Source

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?

Answer

Devart picture Devart · Oct 29, 2012

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.

Related questions

How to call a mysql stored procedure, with arguments, from command line?

How can I call a stored procedure from command line? I have a procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME) NO SQL BEGIN SET @eventIDOut = NULL; IF EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN SELECT `eID` …

Read More
Creating a procedure in mySql with parameters

I am trying to make a stored procedure using mySQL. This procedure will validate a username and a password. I'm currently running mySQL 5.0.32 so it should be possible to create procedures. Heres the code I've used. All I get is …

Read More
MySQL - Define a variable within select and use it within the same select

Is there a possibility to do something like this? SELECT @z:=SUM(item), 2*@z FROM TableA; I always get NULL for the second column. The strange thing is, that while doing something like SELECT @z:=someProcedure(item), 2*@z FROM TableA; …

Read More