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.

score 23 · Answer 1 · edited May 23 '17 at 12:34

23

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');

edited May 23 '17 at 12:34

Community

1
1

answered Mar 15 '11 at 08:16

Jon Black

16,223
5
43
42

My purpose of creating the temporary table is to query it afterwards. I just tried your code and the tmp table you created was not available for query. Instead it gives me a result set. So the procedure just prepares the the employees attendance – burntblark Mar 15 '11 at 08:33
you can do what you suggested but the table will only be available to the connection that created it or called the sproc. I wouldnt recommend this approach but if you could elaborate a bit more on what you're trying to do I might have a few other ideas. You might also want to check this http://pastie.org/1673574 – Jon Black Mar 15 '11 at 08:47
How would you test membership. What if tmp table only has IDs (of authors), and I would like to query something like: "select * from books where author in (tmp)". MySQL returns an error: 'unknown column tmp' – Uri London May 09 '15 at 18:30
One more question about parallelism: It appears 'tmp' is a global object (or has a global scope). For example, if I don't have the line 'drop temporary ...', then the second execution will fail to create tmp. So ... how does the server handle the case when the procedure is executed twice in parallel? – Uri London May 09 '15 at 18:43

score 7 · Answer 2 · answered Jun 03 '13 at 07:47

By default MySQL config variable sql_notes is set to 1.

That means that DROP TEMPORARY TABLE IF EXISTS performance; increments warning_count by one and you get a warning when a stored procedure finishes.

You can set sql_notes variable to 0 in my.cnf or rewrite stored procedure like that:

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

Creating temporary tables in MySQL Stored Procedure

2 Answers2

Linked