Why doesn't this MySQL stored procedure work as I want it to?

I want a stored-procedure to create a temporary table, populate the table with data, then return the table. This is what I have:

CREATE PROCEDURE sp_create_r3(p_panel_id INT)
BEGIN
    -- create new temporary table
    DROP TABLE IF EXISTS temp;
    CREATE TEMPORARY TABLE temp(assembly_id INT, cost1 DECIMAL(10,2), cost2 DECIMAL(10,2));

    -- populate table
    SELECT sf_assembly_breakdown(assembly_id) AS dummy FROM panel_assembly WHERE panel=p_panel_id;

    -- return table
    SELECT * FROM temp;
END

CREATE FUNCTION sf_assembly_breakdown (p_assembly_id INT) RETURNS INT
BEGIN
    DECLARE cost1 DECIMAL(10,2);
    DECLARE cost2 DECIMAL(10,2);

    -- calculate cost1, cost2 here
    ...


    -- insert data into temporary table
    INSERT INTO temp SE开发者_开发技巧LECT p_assembly_id , cost1, cost2;

    -- return dummy value
    RETURN NULL;
END

This doesn't work though! The result set returned by sp_create_r3 is that generated by:

SELECT sf_assembly_breakdown(assembly_id) AS dummy FROM panel_assembly WHERE panel=p_panel_id;

I don't want to return this. I want to return SELECT * FROM temp;

Is this possible? If not what are the alternatives?

I would recommend you set up the temporary table differently:

create temporary table temp select p_assembly_id, cost1, cost from panel_assembly where panel=p_panel_id;

or use SELECT ... INTO syntax.

Your routine will return as many data sets as there are SELECT statements in it. So, the first data set returned is what you indicated, and the second data set returned is what you want.

There are APIs for C and PHP that I know of that are able to deal with multiple result sets returned from a routine, however if you're not using either of those then you may need to look for a library that meets your needs.