>> CREATE PROCEDURE list_user_accounts(IN user_id INT)
>>
>> BEGIN
>>
>> -- Return first result set (single row)
>> SELECT * FROM users WHERE id = user_id;
>>
>> -- Return second result set (zero or more rows)
>> SELECT * FROM accounts WHERE account_holder = user_id;
>>
>> END;
>
> I'd say returning multiple recordset is useful to save connections
> and transferred data.
> You can't get the same with a left join (users fields will be
> repeated over and over) and you can't get the same with 2 separated
> statements since they will need 2 connections.
>
> But from the client side, suppose it PHP... if the first
> statement return no record and the second one return 3 records, how
> can I know?
Well, (in MySQL at least) in that case you're still going to get a
result set, it's just going to be an empty one (result with no rows).
So, no matter how many rows the SELECT statements resolve to, you're
always going to get two result sets :)
Vladimir
--
Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
