I'm writing a function that I need to use either a TABLE variable for (I hear they don't exist in MySQL) or a temporary table.
However, it seems that temporary tables only seem to work in stored procedures, not functions. I keep getting this error:
Explicit or implicit commit is not allowed in stored function or trigger.
What I'm trying to build is a solution to an earlier question of mine. 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.
My intention is to use this as part of another queries WHERE clause, so it needs to be a function and not a stored procedure.
How can I use temporary tables in stored functions? And if I can't, what can I do instead?
Here's my (currently broken) function (or as a gist):
-- 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 < @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 >= 1 THEN RETURN 1;
ELSE RETURN 0;
END//
DELIMITER ;