DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_fail_temp_table`$$

CREATE DEFINER=`xpto`@`%` PROCEDURE `sp_fail_temp_table`()

BEGIN

	/**

	 * Creating temporary table

	 */

	DROP TEMPORARY TABLE IF EXISTS tbl_temporary;

	CREATE TEMPORARY TABLE IF NOT EXISTS tbl_temporary

	(

		`a` VARCHAR(64)

	)

	;

	/**

	 * Creating second temporary table

	 * the call to a function makes it fail on the second time

	 * this stored procedure is called

	 */

	DROP TEMPORARY TABLE IF EXISTS tbl_second_temp;

	CREATE TEMPORARY TABLE tbl_second_temp

	SELECT 

		fn_return_zero() AS temp_amount

	FROM

		(

		SELECT

			a

		FROM

			tbl_temporary tt

		) tt

	ORDER BY

		a

	;

END$$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS fn_return_zero$$

CREATE DEFINER=`xpto`@`%` FUNCTION fn_return_zero() RETURNS FLOAT(10,2)

    READS SQL DATA

BEGIN

	RETURN 0;

END $$

DELIMITER ;

CALL sp_fail_temp_table();