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)

BEGIN

 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 ;
 
END

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.

Thanks
A Rahim Khan

Advertisements
    • 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:

        http://www.sommarskog.se/share_data.html

        Regards,

        Ruben.

    • 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???

    Ex:

    CREATE VIEW MyView AS
    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: