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.
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