Creating temporary tables in MySQL Stored Procedure

Question

The following procedure gives me an error when I invoke it using the CALL statement:

 CREATE DEFINER=`user`@`localhost` PROCEDURE `emp_performance`(id VARCHAR(10)) BEGIN DROP TEMPORARY TABLE IF EXISTS performance; CREATE TEMPORARY TABLE performance AS       SELECT time_in, time_out, day FROM attendance WHERE employee_id = id; END 

The error says "Unknown table 'performance' ".

This is my first time actually using stored procedures and I got my sources from Google. I just cant figure out what I am doing wrong.

Answer 1

I've tidied it up a little for you and added example code. I always keep my parameter names the same as the fields they represent but prefix with p_ which prevents issues. I do the same with variables declared in the sproc body but prefix with v_.

You can find another one of my examples here:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

drop procedure if exists emp_performance;  delimiter #  create procedure emp_performance ( in p_employee_id varchar(10) ) begin  declare v_counter int unsigned default 0;  create temporary table tmp engine=memory select time_in, time_out   from attendance where employee_id = p_employee_id;  -- do stuff with tmp...  select count(*) into v_counter from tmp;  -- output and cleanup  select * from tmp order by time_in;  drop temporary table if exists tmp;  end#  delimiter ;  call emp_performance('E123456789');