Using Temporary Tables

Temporary tables allow you to store and process interim results within a report. They are connection-specific, meaning multiple users can use temporary tables with the same name without conflict.

delimiter $$
drop procedure if exists sp_DBR_fruits
$$
create procedure sp_DBR_fruits()
begin

drop temporary table if exists fruits_tmp;
create temporary table fruits_tmp (
  fruit varchar(30)
);

insert into fruits_tmp (fruit)
select 'banana'
union
select 'orange'
union
select 'lemon';

insert into fruits_tmp (fruit) 
values ('apple');

select count(*) as 'fruits' from fruits_tmp;

drop temporary table fruits_tmp;

end
$$

CALL sp_DBR_fruits();
+--------+
| Fruits |
+--------+
|      4 | 
+--------+
1 row in set (0.00 sec)