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

Question

I want to insert the results of a stored procedure into a temporary table, like this:

CREATE temporary TABLE NEWBalance (VendorAmount NUMERIC(15,2),   
                                   UserBalanceAmount NUMERIC(15,2));  

INSERT NEWBalance call SP VenAccNo,PeopleId;

But that produces 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 a way to do this?

Answer 1

Unfortunately you still can't do that in MySql.

A possible solution is to modify your SP and make it do INSERT into a temporary table.

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

Then your flow is 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;