title: Deleting temporary tables with a stored procedure
body: <p>I have been trying to make a stored procedure which autodeletes temporary tables.</p>
  
  <pre><code>CREATE PROCEDURE DeleteTemp()
  BEGIN
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  
  
  DECLARE tmptablename VARCHAR(100);
  DECLARE tmpTables CURSOR FOR
      SELECT TABLE_NAME
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME LIKE 'tmp%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
  OPEN tmpTables;
  SELECT FOUND_ROWS() into num_rows;
  the_loop: LOOP
      FETCH tmpTables INTO tmptablename;
      DROP TABLE tmptablename;
      IF no_more_rows THEN
          CLOSE tmpTables;
          LEAVE the_loop;
      END IF;
  END LOOP the_loop;
  END
  </code></pre>
  
  <p>However, all I get is:</p>
  
  <p>Query : call DeleteTemp 
  Error Code : 1051
  Unknown table 'tmptablename'</p>
  
  <p>How can I pass the variable tmptablename properly into the "DROP TABLE" command?</p>
  
  <hr>
  
  <p>Fixed using prepared statements.</p>
  
  <pre><code>CREATE
      PROCEDURE DeleteTemp()
      BEGIN
      DECLARE no_more_rows BOOLEAN;
      DECLARE loop_cntr INT DEFAULT 0;
      DECLARE num_rows INT DEFAULT 0;
  
  
      DECLARE tmptablename VARCHAR(100);
      DECLARE tmpTables CURSOR FOR
          SELECT TABLE_NAME
          FROM information_schema.TABLES
          WHERE TABLE_SCHEMA='MY_SCHEMA'
              AND TABLE_NAME LIKE 'tmp%';
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
      OPEN tmpTables;
      SELECT FOUND_ROWS() INTO num_rows;
  
      the_loop: LOOP
          FETCH tmpTables INTO tmptablename;
          IF no_more_rows THEN
              CLOSE tmpTables;
              LEAVE the_loop;
          ELSE
              SET @a:=CONCAT('DROP TABLE ',tmptablename);
              PREPARE stmt1 FROM @a;
              EXECUTE stmt1;
              DEALLOCATE PREPARE stmt1;
          END IF;
      END LOOP the_loop;
      END
  </code></pre>