MySQL stored procedures can use SELECT statements in their body, returning a data set to the caller. The
manual also explains that multiple data sets can be returned from such a stored procedure.
For example, assume you have this stored procedure:
CREATE PROCEDURE p1(x INT, y INT)
DETERMINISTIC
BEGIN
SELECT
x ;
SELECT
x AS first_param,
y AS second_param;
SELECT
x,
y,
x + y AS sum_xy,
x * y AS prod_xy;
SELECT * FROM t1;
END
There are four SELECT statements, with different headers, and a variable number of rows returned.
Early versions of
DBD::mysql could not handle multiple data sets. Actually, prior to version 3.0004 they could not even handle one dataset from a stored procedure. Starting with version 4.001, single and multiple datasets from SPs are handled gracefully.
The trick is to call
$sth->more_results
after retrieving each dataset.
See the complete example at
Multiple data sets in MySQL stored procedures![MySQl Users Conference and Expo MySQl Users Conference and Expo](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sv5VAkYlUUJIBapz9WQZp5ZGNj8n3CLEKnOoxqFYS7ruRw0EZEYV3eVoCfkXShB7d6xEhFoN5JiITjGs669XTf7xzkVu8Fg4sgLI36LwQua5A7ctmGZXr8qYP4EelPmfcewmkPECwtzfin3-mw-dfc5TVW=s0-d)
For those interested, the
MySQL Users Conference and Expo includes a
BoF about DBD::mysql on April 24
th at 7:30pm.