MySQL Stored procedure : Variable set is null though query returns results

Question

I'm having problems with stored procedures, this part to me more specific :

   CREATE FUNCTION calculCommission
   (
          idprogramV INT
          , idmodeV INT
          , amount DECIMAL(10,2)
          , amount_total DECIMAL(10,2)
          ,idformat INT
   )
   RETURNS DECIMAL(10,2)
   BEGIN
      DECLARE commission DECIMAL(10,2);
      DECLARE total_amount_default DECIMAL(10,2);
      IF amount_total IS NULL OR amount_total = '' THEN

          SET total_amount_default = (SELECT CONVERT(`value`+commission,DECIMAL(10,2)) as default_amount 
                                      FROM remunerations r 
                                      JOIN groups g 
                                             on g.idgroup = r.idgroup 
                                      WHERE g.idprogram=idprogramV 
                                             AND r.idmode=idmodeV 
                                             AND g.`default`=1);

          SET commission = (total_amount_default - amount);

      ELSE
          SET commission = amount_total - amount;

      END IF;
     RETURN commission;
   END$$

First, i realized that passing a NULL value to for the "amount_total" will not be correctly parsed by the procedure. IF amount_total IS NULL would return false if the amount_total parameter was set to NULL, BUT it is considered an empty string. Weird, but i worked around that using "OR amount_total = ''.

Now my problem is that SET total_amount_default line. When i manually execute the query that should be fetched into the variable in a SQL client, i get a result (0.56). But in the stored procedure, it always returns NULL.

I am wondering if that has someting to do with the data types, so i tried to convert the result, or with the variables that are passed to the query. But i have other procedures that give correct result with the exact same type of expression.

I am scratching my head right there, and have no clue on how to solve that issue.

Thanks for your help

Edit : i added some debug to see what's going on: idprogramV value, just before trying the query is : 4258 idmodeV value, same time is : 1 using the query with these parameters returns 0.52, but i tried inserting the result directly into a table to see what it looks like, and i get NULL... Really i have no idea on what's wrong, i'm guessing it's the replacement of the variable for the query execution.

Edit2 : Okaaaaaay newbie mistake here.

I am requesting a field that has the same name as a declared variable in my stored procedure.

The variable is used in the query instead of the table field.

Thanks me !

you should submit an answer for a shot at the self-learner badge if you don't have it :) — Poodlehat
– Poodlehat, Commented Mar 7, 2012 at 15:50

user1159791 · Accepted Answer · 2012-03-26 16:15:02Z

1

Edit2 : Okaaaaaay newbie mistake here.

I am requesting a field that has the same name as a declared variable in my stored procedure.

The variable is used in the query instead of the table field.

Thanks me !

(badge hunting right there ;))

answered Mar 26, 2012 at 16:15

user1159791

5051 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bfavaretto · Accepted Answer · 2012-03-07 16:00:14Z

0

Use this to assign to your variable from the query:

SELECT total_amount_default = CONVERT(`value`+commission,DECIMAL(10,2))
FROM remunerations r 
JOIN groups g 
      on g.idgroup = r.idgroup 
WHERE g.idprogram=idprogramV 
      AND r.idmode=idmodeV 
      AND g.`default`=1);

answered Mar 7, 2012 at 16:00

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Collectives™ on Stack Overflow

MySQL Stored procedure : Variable set is null though query returns results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related