title: How to use DROP TABLE IF EXISTS in a MySQL Stored Procedure
body: <p>I want to know how to use <code>DROP TABLE IF EXISTS</code> 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. </p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <p>Here are my examples:</p>
  
  <p><strong>This Works:</strong></p>
  
  <pre><code>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();
  </code></pre>
  
  <p><strong>This Works:</strong></p>
  
  <pre><code>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();
  </code></pre>
  
  <p><strong>This does not:</strong></p>
  
  <pre><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 ;
  CALL pTest();
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Dev Server: mySQL Server version: 5.1.47-community
  Prod Server: mySQL Server version: 5.0.45-log</p>
  
  <p>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?</p>
  
  <p>Thanks.</p>