I always get interesting feedback on some posts. On my test case for discovering the STR_TO_DATE function’s behavior, the comment was tragically valid. I failed to cleanup after my test case. That was correct, and I should have dropped param table and the two procedures.
While appending the drop statements is the easiest, I thought it was an opportunity to have a bit of fun and write another procedure that will cleanup test case tables within the test_month_name procedure. Here’s sample dynamic drop_table procedure that you can use in other MySQL stored procedures:
CREATE PROCEDURE drop_table
( table_name VARCHAR(64))
BEGIN
/* Declare a local variable for the SQL statement. */
DECLARE stmt VARCHAR(1024);
/* Set a session variable with two parameter markers. */
SET @SQL := CONCAT(‘DROP TABLE ‘,table_name);
/* Check if the constraint exists. */
IF EXISTS (SELECT NULL
FROM information_schema.tables t
WHERE t.table_schema = database()
AND t.table_name = table_name)
THEN
/* Dynamically allocated and run statement. */
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;
$$
You can now put a call to the drop_table procedure in the test_month_name procedure from the earlier post. For convenience, here’s the modified test_month_name procedure with the call on line #33 right before you leave the loop and procedure:
CREATE PROCEDURE test_month_name()
BEGIN
/* Declare a handler variable. */
DECLARE month_name VARCHAR(9);
/* Declare a handler variable. */
DECLARE fetched INT DEFAULT 0;
/* Cursors must come after variables and before event handlers. */
DECLARE month_cursor CURSOR FOR
SELECT m.month_name
FROM month m;
/* Declare a not found record handler to close a cursor loop. */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
/* Open cursor and start simple loop. */
OPEN month_cursor;
cursor_loop:LOOP
/* Fetch a record from the cursor. */
FETCH month_cursor
INTO month_name;
/* Place the catch handler for no more rows found
immediately after the fetch operations. */
IF fetched = 1 THEN
/* Fetch the partial strings that fail to find a month. */
SELECT * FROM param;
/* Conditionally drop the param table. */
CALL drop_table(‘param’);
/* Leave the loop. */
LEAVE cursor_loop;
END IF;
/* Call the subfunction because stored procedures do not
support nested loops. */
CALL read_string(month_name);
END LOOP;
END;
$$
As always, I hope sample code examples help others solve problems.