MySQL: how to insert the result of a stored procedure into a temporary table

Question

I want to insert the results of a stored procedure into a temporary table, for example:

CREATE temporary TABLE NEWBalance (VendorAmount NUMERIC(15,2), UserBalanceAmount NUMERIC(15,2)); INSERT NEWBalance call SP VenAccNo,PeopleId; 

But this causes an error:

 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call SP VenAccNo,PeopleId' at line 1 

Is there any way to do this?

Answer 1

Unfortunately, you still cannot do this in MySql.

A possible solution is to modify your SP and include INSERT in the temporary table.

 CREATE PROCEDURE your_sp(...) BEGIN -- do your processing ... -- insert results into a temporary table INSERT INTO NEWBalance ... SELECT ...; END 

Then your stream looks like this

 CREATE temporary TABLE NEWBalance ( VendorAmount NUMERIC(15,2), UserBalanceAmount NUMERIC(15,2) ); CALL your_sp (...); -- do your processing on data in a temporary table ... DROP TEMPORARY TABLE NEWBalance;