Menu
How do you return multiple result sets from a MYSQL Stored Procedure?
This is my test stored proc:
DELIMITER $$
CREATE DEFINER=`hlamAdmin`@`%` PROCEDURE `test`()
BEGIN
SELECT *
FROM hlam.member;
SELECT *
FROM hlam.security;
END
Now when I call this:
Call test()
I only receive one resultset. How do I receive both? I am used to MSSQL I apologize if this is an easy question.
599
Most stored procedures return multiple result sets. Such a stored procedure usually includes one or more select statements. The consumer needs to consider this inclusion to handle all the result sets.
MySQL stored function returns only one value. To develop stored programs that return multiple values, you need to use stored procedures with INOUT or OUT parameters. If you are not familiar with INOUT or OUT parameters, check it out the stored procedure's parameters tutorial for the detailed information.
In order to fetch the multiple returned values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword. You can also make use of the Split function to split the comma separated (delimited) values into rows.
You could try to either JOIN them (Linking them together) or use UNION (Combine two selects in one);
http://dev.mysql.com/doc/refman/5.0/en/join.html
select a.col1, b.col1
from table1 a
inner join table2 b on a.id = b.id;
http://dev.mysql.com/doc/refman/5.0/en/union.html
select name as col1, surname as col2 from table1
union
select location as col1, desc as col2 from table2;
John
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
