How do you use DROP TABLE IF EXISTS in a mySQL Stored Procedure?

Question

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. 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:

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

I don't know why this is not working for you,but you should be able to work around the issue using a continue handler. If you put the DROP TABLE statement into it's own BEGIN...END block you can use a continue handler to ignore the error if the table does not exist.

Try this:

DELIMITER //
    DROP PROCEDURE IF EXISTS pTest //
    CREATE PROCEDURE pTest()
    BEGIN
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;
        DROP TEMPORARY TABLE tblTest;
      END;
        CREATE TEMPORARY TABLE tblTest (
            OrderDate varchar(200)
        );
    END //
 DELIMITER ;
CALL pTest();