I have been reading through Multiple data sets in MySQL stored procedures and am seeking clarification of exactly what is going on.

The example uses a loop whilst $sth->more_results. This suggests a situation where the number of result sets is unknown. The procedure that I have in mind would return a fixed number of result sets so I was wondering if I could simplify to this (assumes three result sets returned):

my $sth=prepare->('call my_sp($some_number);');
$sth->execute();
my $foo=$sth->fetchall_arrayref();
my $bar=$sth->fetchall_arrayref();
my $baz=$sth->fetchall_arrayref();
[download]

...or whether I have got the wrong end of the stick entirely.

Update

Having played around a bit, I have found that that $sth->more_results() must be called before the next result set appears so the code above does not work. This works:

my $sth=prepare->('call my_sp($some_number);');
$sth->execute();
my $foo=$sth->fetchall_arrayref();
$sth->more_results();
my $bar=$sth->fetchall_arrayref();
$sth->more_results();
my $baz=$sth->fetchall_arrayref();
[download]

I don't have time to write up a full example at present but can summarise that the only difference between retrieving multiple results sets is that you have to call $sth->more_results() between them.

There is now a node as a response to the original providing a cross-reference to this one.