Re: Temporary Tables Aliasing in Stored Procedure

[Roland Bouman]

Hi!

there is a particular language construct, but you can only use that in DDL when dropping the table:

drop temporary table if exists MY_TEMPORARY TABLE

so, if it's limited to that, use it. If it is your intention to perform another statement or statement sequence, you could do it with the information_schema:

create procedure test_exists()
if exists (
select null
from information_schema.tables
where table_schema = schema()
and table_name = 'testje'
) then
select *
from testje;
else
select 'nope';
end if;


but alas, this does not work for temporary tables.

There is a way out after all:

create procedure test_exists_temporary()
begin
declare v_exists bool default true;
begin
declare v_count int;
declare exit handler for sqlexception
set v_exists := false;
select null
into v_count
from my_temporary_table
limit 0
;
end;
select v_exists;
end;
//

mysql> call test_exists_temporary()
-> //
+----------+
| v_exists |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table my_temporary_table (c int)
-> //
Query OK, 0 rows affected (0.34 sec)

mysql> call test_exists_temporary()
-> //
+----------+
| v_exists |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

To be really really sure, see if you can declare the handler for an appropriate SQLSTATE corresponding to table existence (dont know if its there).

Good luck!