CODEWITHSUNDEEP
mysqlstored-procedures
user1159791
user1159791

Reputation: 505

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

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 !

Upvotes: 2

Views: 7327

Answers (2)

user1159791
user1159791

Reputation: 505

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 ;))

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71939

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);

Upvotes: 0

Related Questions