MySQL Stored Function that returns Table

Don’t be misguided by the posting title. It’s not possible to write Table-Valued Stored Function in MySQL Server. MySQL Stored Function returns single values only. However, if the situation demands a Function to return a set of Data, you have only two choices.

You may create a string using some separators with your result set and return it to the caller. In this case, you need to use a string parser to retrieve Data Set as well. This will involve both of application’s back-end and front-end to collect your desired Data Set.

Another good alternative is to use Temporary Table. Let me explain it with some coding.

CREATE FUNCTION sf_collect_order_details_id (param_order_master_id INT)
RETURNS tinyint(1)


 DECLARE sf_result INT DEFAULT 0; 
 DELETE FROM temp_table;
 INSERT INTO temp_table 
 SELECT order_details_id FROM table_order_details 
 WHERE order_master_id = param_order_master_id;
 SET sf_result = 1;
 RETURN sf_result ;

You have to cerate "temp_table" Table first according to your selected Data Set. Call "sf_collect_order_details_id" in the following way.

SELECT sf_collect_order_details_id(1);

Finally, if "sf_collect_order_details_id" returns 1, you have your Data Set ready in "temp_table". So, you can retrieve your Data Set from "temp_table". In this example, I set very simple Data selection criteria. it's better to use Stored Function if you have to do a complex selection.

A Rahim Khan

    • George Kalash
    • November 25th, 2011

    Dear Mr. Rahim,

    What if the function was called by different processes? temp_table data would irrelevant. no?

    • yeah, that’s a big issue. This will mess up if called simultaneously by different processes. There is one way, create new table each time i.e; with time stamp ,when this function is called, insert data into it and return the table name.

      Get your data from that table, drop it when you are done. I am not in touch with MySQL Server these days, there may be some other ways out there.

        • Ruben Ramirez
        • February 1st, 2012

        Dear Rahim:

        Thanks for the post. In the case of concurrent calls, I think you can also rely on process-keyed tables. Please take a look at the corresponding section on the the following link:



    • Guillermo Malagón
    • February 18th, 2016

    Hello A Rahim, im having an lag issue with a query im working on, can i create a view where i send a different value everytime and gain some speed???


    SELECT Column FROM Table WHERE Value = @MyVariable;

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: