Passing values between stored procedures

[paul.beaney]

Got a weird one with stored procedures and passing values between then using parameters.

Procedure1 : Looks up the price of a given product (given a certain quantity) and passes the price back through an OUT parameter 'Price' (no resultset returned - no SELECT anywhere). The procedure looks something like this:

Procedure1 (in ProdCode varchar(10), in Qty integer, out Price integer)
BEGIN
/* Lookup price with a couple of queries */

set Price = [some value];
END


This works fine from the MySQL command line using:

Call Procedure1('Code', 1, @a);
select @a;

...although MySQL gives a warning saying 'Error 1329 No data to FETCH'.



Procedure 2 : Invokes Procedure 1 a number of times (not iteratively) and looks something like this:

Procedure2 (in ProdCode, out Price integer, out Qty )
BEGIN
DECLARE Price integer;

/* Find Qty1 */
Call Procedure1(ProdCode, Qty1, @Price);
Set Qty = Qty1;
Select Price, Qty;

/* Find Qty2 */
Call Procedure1(ProdCode, Qty2, @Price);
Set Qty = Qty2;
Select Price, Qty;

END

To make the procedure create multiple recordsets, I have used SELECT to return the values. However, Procedure2 doesn't seem to be getting the values back from Procedure1 as 'Price' is always null (Qty works ok as that it determined by a SELECT INTO within Procedure2). However, if I replace @Price with an arbitrary (non-declared) variable such as '@a', and then SET Price = @a, I *do* get the value back. Surely local variables ought to be assignable when used as return parameters when calling procedures from within procedures ?

Can anyone shed some light ?

Thanks,

- Paul