Returning multiple result sets from a MYSQL stored procedure

Question

How do you return multiple result sets from a MYSQL stored procedure?

This is my test stored process:

DELIMITER $$ CREATE DEFINER=`hlamAdmin`@`%` PROCEDURE `test`() BEGIN SELECT * FROM hlam.member; SELECT * FROM hlam.security; END

Now when I call it:

 Call test()

I get only one set of results. How to get both? I am used to MSSQL. I apologize if this is a simple question.

+6

jtmg.io 10 sept. '11 at 23:30

2 answers

John · Answer 1 · 2011-09-28T17:08:35+0000

You can either try to attach them (tie them together) or use UNION (combine two selections in one);

 select a.col1, b.col1 from table1 a inner join table2 b on a.id = b.id;

 select name as col1, surname as col2 from table1 union select location as col1, desc as col2 from table2;

John

Anderson · Answer 2 · 2011-12-28T21:29:03+0000

Using HeidiSQL (the free MySQL client), I get both result sets, but on two different tabs.