How to use DROP TABLE IF EXISTS in a MySQL Stored Procedure

Question

I want to know how to use DROP TABLE IF EXISTS in a MySQLstored procedure. I'm writing a rather long mySQL Stored Procedure that will do a bunch of work and then load up a temp table with the results. However, I am having trouble making this work.

I've seen a few ways to do the temp table thing. Basically, you either create the temp table, work on it, and then drop it at the end ... or you drop it if it exists, create it, and then do your work on it.

I prefer the second method so that you always start of clean, and it's a built-in check for the table's existence. However, I can't seem to get it to work:

Here are my examples:

This Works:

DELIMITER//
    DROP PROCEDURE IF EXISTS pTest//
    CREATE PROCEDURE pTest()
    BEGIN
        CREATE TEMPORARY TABLE tblTest (
            OrderDate varchar(200)
        );
        DROP TEMPORARY TABLE tblTest;
    END//
 DELIMITER ;
CALL pTest();

This Works:

DELIMITER//
    DROP PROCEDURE IF EXISTS pTest//
    CREATE PROCEDURE pTest()
    BEGIN
        DROP TEMPORARY TABLE tblTest;
        CREATE TEMPORARY TABLE tblTest (
            OrderDate varchar(200)
        );
    END//
 DELIMITER ;
CALL pTest();

This does not:

DELIMITER//
    DROP PROCEDURE IF EXISTS pTest//
    CREATE PROCEDURE pTest()
    BEGIN
        DROP TEMPORARY TABLE IF EXISTS tblTest;
        CREATE TEMPORARY TABLE tblTest (
            OrderDate varchar(200)
        );
    END//
 DELIMITER ;
CALL pTest();

The first 2 work, but if that table exists (like if the procedure didn't finish or something), it'll obviously end with a "Table tblTest does not exist" error. The non-working example is what I'm looking for -- drop the table if its there and then recreate it so that I can start clean.

It feels like it's the "IF EXISTS" making this thing fail. I've copied code from all sorts of sites that do things very similar and in no case can I get a "DROP TABLE IF EXISTS..." to work. Ever.

Dev Server: mySQL Server version: 5.1.47-community Prod Server: mySQL Server version: 5.0.45-log

We can't change db versions (DBAs won't allow it), so I'm stuck on what I have. Is this a bug in mySQL or in the Procedure?

Thanks.

Answer 1

It's an old question but it came up as I was looking for DROP TABLE IF EXISTS.

Your non-working code did not work on my MySQL 5.1.70 server.

All I had to do was add a space between DELIMITER and // on the first line, and everything worked fine.

Working code:

DELIMITER //
    DROP PROCEDURE IF EXISTS pTest//
    CREATE PROCEDURE pTest()
    BEGIN
        DROP TEMPORARY TABLE IF EXISTS tblTest;
        CREATE TEMPORARY TABLE tblTest (
            OrderDate varchar(200)
        );
    END//
DELIMITER ;