title: Temporary Table in Stored Functions?
body: <p>I'm writing a function that I need to use either a <code>TABLE</code> variable for (<a href="https://stackoverflow.com/questions/1524858/create-table-variable-in-mysql">I hear they don't exist in MySQL</a>) or a temporary table.</p>
  
  <p>However, it seems that temporary tables only seem to work in stored procedures, not functions. I keep getting this error:</p>
  
  <blockquote>
    <p>Explicit or implicit commit is not allowed in stored function or
    trigger.</p>
  </blockquote>
  
  <hr>
  
  <p>What I'm trying to build is a solution to <a href="https://stackoverflow.com/questions/8015018/mysql-in-clause-for-dates-between-a-start-and-end-date">an earlier question of mine</a>. It's a function that receives a start date, an end date, and a comma-deliminated string. It first finds all the months between the start and end date and saves them as individual records in the first temporary table. It then parses out the comma-deliminated string and saves those into a second temporary table. Then it does a select join on the two, and if records are present, it returns true, otherwise false.</p>
  
  <p>My intention is to use this as part of another queries <code>WHERE</code> clause, so it needs to be a function and not a stored procedure.</p>
  
  <p>How can I use temporary tables in stored functions? And if I can't, what can I do instead?</p>
  
  <p>Here's my (currently broken) function (or as a <a href="https://gist.github.com/1342136" rel="nofollow noreferrer">gist</a>):</p>
  
  <pre><code>-- need to parse out a string like '4,2,1' and insert values into temporary table
  -- MySQL doesn't have a native string split function, so we make our own
  -- taken from: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
  DROP FUNCTION IF EXISTS SPLIT_STR;
  CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)
  RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
  
  -- need to find all months between the start and end date and insert each into a temporary table
  DROP FUNCTION IF EXISTS months_within_range;
  
  DELIMITER //
  
  CREATE FUNCTION months_within_range(starts_at DATE, ends_at DATE, filter_range VARCHAR(255)) RETURNS TINYINT
  BEGIN
  
      DROP TABLE IF EXISTS months_between_dates;
      DROP TABLE IF EXISTS filter_months;
      CREATE TEMPORARY TABLE months_between_dates (month_stuff VARCHAR(7));
      CREATE TEMPORARY TABLE filter_months (filter_month VARCHAR(7));
  
      SET @month_count = (SELECT PERIOD_DIFF(DATE_FORMAT(ends_at, "%Y%m"), DATE_FORMAT(starts_at, "%Y%m")));
      -- PERIOD_DIFF only gives us the one month, but we want to compare to, so add one
      -- as in, the range between 2011-01-31 and 2011-12-01 should be 12, not 11
  
      INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at, "%Y-%m"));
      SET @month_count = @month_count + 1;
      -- start he counter at 1, since we've already included the first month above
      SET @counter = 1;
      WHILE @counter &lt; @month_count DO
          INSERT INTO months_between_dates (month_stuff) VALUES (DATE_FORMAT(starts_at + INTERVAL @counter MONTH, "%Y-%m"));
          SET @counter = @counter + 1;
      END WHILE;
  
      -- break up the filtered string
      SET @counter = 1;
      -- an infinite loop, since we don't know how many parameters are in the filtered string
      filters: LOOP
          SET @filter_month = SPLIT_STR(filter_range, ',', @counter);
          IF @filter_month = '' THEN LEAVE filters;
          ELSE
              INSERT INTO filter_months (filter_month) VALUES (@filter_month);
              SET @counter = @counter + 1;
          END IF;
      END LOOP;
  
      SELECT COUNT(*) INTO @matches FROM months_between_dates INNER JOIN filter_months ON months_between_dates.month_stuff = filter_months.filter_month;
  
      IF @matches &gt;= 1 THEN RETURN 1;
      ELSE RETURN 0;
  
  END//
  
  DELIMITER ;
  </code></pre>