Scope of temp tables created in MySQL stored procedure

Question

QUESTION WITHDRAWN! When I spell everything correctly, the problem goes away!

I have a MySQL stored procedure which creates a temporary table. When I call the procedure from the mysql prompt, it appears to run successfully, but if I then SELECT COUNT(*) from the temporary table, I get an error saying the table doesn't exist.

Does a temporary table created inside a stored procedure cease to exist when the stored procedure ends?

mysql> delimiter //
mysql> drop procedure if exists sp_temp_reciepts//
mysql> create procedure sp_temp_receipts ()
begin
  drop temporary table if exists receipts;
  create temporary table receipts
  (  ... snip ...
  );
  insert into receipts
  select ... snip ...
end//
mysql> delimiter ;
mysql> call sp_temp_reciepts();
Query OK, 46903 rows affected, 1 warning (2.15 sec)
mysql> select count(*) from receipts;
ERROR 1146 (42S02): Table 'receipts' doesn't exist

delimiter // drop procedure if exists sp_temp_reciepts// create procedure sp_temp_receipts () begin — user1956095, Jan 07 '13 at 20:23
You have a wild mix of spelling in your SP. `reciepts` vs `receipts`. — favoretti, Jan 07 '13 at 20:34
In short, the table will continue to exist. Just run `drop procedure if exists f(); delimiter $ create procedure f()begin drop temporary table if exists t; create temporary table t select 1; select*from t; end$ delimiter ; call f(); select count(*)from t;` — Pacerier, May 08 '15 at 08:50

score 16 · Answer 1 · edited Apr 13 '17 at 12:42

This question is over a year old. It deserves an answer. Here it goes:

I just saw this question today in the DBA StackExchange : How long will a temporary MEMORY table persist if I don't drop it (MySQL). I just answered it. In part I said this:

According to the Book

kdsjx

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.

It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Although the question was dropped because of spelling the table wrong, this viewpoint needs to expressed and learned by all who call MySQL Stored Procedures and need their data available.

Scope of temp tables created in MySQL stored procedure

1 Answers1