MySQL temporary vs memory table in stored procedures

A temporary table will only exist for the duration of your session. A table declared with Engine=Memory will persist across user sessions / connections but will only exist in the lifetime of the MySQL instance. So if MySQL gets restarted the table goes away.

Of the two, I'd use a temporary table for report.

A memory table holds data across user sessions & connections, so you'd have to truncate it every time to make sure you wouldn't be using data from someone else. Assuming you put in whats necessary to maintain a memory table depending on your needs, it's fine - the temp table is a little safer from a maintenance perspective.

Why is this restricted to just the two options? You can do:

CREATE TEMPORARY TABLE t (avg double) ENGINE=MEMORY;

Which works, although I'm not sure how to check if the memory engine is actually being used here.