Cannot insert value in the Temporary Table when accessed from a function via a procedure.

[Rajarshi Biswas]

Hi All,

I am quite confused about a problem which i am facing now, i dont know whether i should put it as a bug or not.
The problem is as follows
Cannot insert in the Temporary Table when accessed from a function via a procedure. Whereas, when the procedure is called alone, the desired result is obtained.
to be more elaborate.

i have a procedure which creates a temporary table and calls another procedure which inserts some value in that temporary table created in prc_1.

CREATE PROCEDURE prc_1()

BEGIN

CREATE TEMPORARY TABLE Temporary_Table
(id VARCHAR(32), kind INT(5));
-- calls the 2nd procedure here
call prc_2();
END;
//


CREATE PROCEDURE prc_2()

BEGIN
DECLARE v_id VARCHAR(32);
DECLARE v_kind INT(3);
DECLARE c_level CURSOR FOR SELECT prodname,prodprice FROM product limit 1;
OPEN c_level;

FETCH c_level INTO v_id,v_kind;
CLOSE c_level;
INSERT INTO Temporary_Table VALUES (v_id, v_kind);

END;
//


When i say -> call prc_1() then it produces the desired result .

But when i call prc_1 () from a function, it says Temporary_Table not found.

eg:

CREATE FUNCTION func_1()
returns VARCHAR(32)
BEGIN

DECLARE ret VARCHAR(32);

SET ret= ' return some value ';
CALL prc_1();
return ret;

END;

//
it gives an error message saying databasename.Temporary_Table is not found.


Please help with some suggestion or tips on this.

Thanks in Advance