Selecting MySQL session variables multiple times

Question

Having a weird problem selecting session variables which are changed by a stored procedure.

 call calculate("input1", "input2",@price);
 select @price; /*price is 2.00*/


 call calculate("anotherinput","anotherinput2",@price);
 select @price; /*price is 3.00*/

However, the answer I get is actually 2 3.00's rather than the 2.00 then the 3.00. Assuming it's getting overwritten and messing it all up. But what's the best way to go about this?

Post the contents of the stored procedure – Lucas Henrique Apr 15 '14 at 16:38 — Lucas Henrique, Apr 15 '14 at 16:38
has an INOUT of fare and a @price set above it – user3504452 Apr 15 '14 at 16:44 — user3504452, Apr 15 '14 at 16:44

score 0 · Answer 1 · answered Apr 15 '14 at 16:52

0

Procedure might have not defined the parameter price as an OUT parameter, but IN parameter. Unless which the passed IN parameter will always show the same OLD passed value.

Working Example:

mysql> create procedure test_sp( inout p int, in q int ) set p := p*q;
mysql> set @p := 4; set @q := 6;
mysql> delimiter //
mysql> call test_sp( @p, @q );
    -> select @p, @q;
    -> call test_sp( @p, @q );
    -> select @p, @q;
    -> //

+------+------+
| @p   | @q   |
+------+------+
|   24 |    6 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| @p   | @q   |
+------+------+
|  144 |    6 |
+------+------+
1 row in set (0.01 sec)

answered Apr 15 '14 at 16:52

Ravinder Reddy

23,692
6
52
82

I'm actually using INOUT just like your example, strange. – user3504452 Apr 15 '14 at 17:04
Can you post signature of SP. `procedure sp_name( in... )` – Ravinder Reddy Apr 15 '14 at 17:10
And in one of the comments on your posting, you said `inout` was on `fare`. What about `price`? You better post the SP's signature. – Ravinder Reddy Apr 15 '14 at 17:12
SET @cost = 00.00; DROP PROCEDURE IF EXISTS calculate; DELIMITER // CREATE PROCEDURE calculate ( IN input1 VARCHAR(128), IN input2 VARCHAR(128), INOUT fare DECIMAL (30,2) ) – user3504452 Apr 15 '14 at 17:12
Perhaps you are not resetting the fare value in the SP body. Check it. – Ravinder Reddy Apr 15 '14 at 17:15
May be on a condition skip on other parameters, fare was un-altered. – Ravinder Reddy Apr 15 '14 at 17:16
as in i should do set fare = 0 in the SP? edit - doesnt seem to make a difference – user3504452 Apr 15 '14 at 18:39
is it because my input1 and inpit2 for example is how i'm calculating the price and they are just IN rather than INOUT? getting errors now with INOUT though – user3504452 Apr 15 '14 at 18:43
i'm not setting @fare in the SP, just fare. but this also doesnt seem to make a difference. – user3504452 Apr 15 '14 at 18:53
Can you post your SP body, so it would be mush easier to figure out what is wrong with. – Ravinder Reddy May 30 '14 at 19:50

Selecting MySQL session variables multiple times

1 Answers1