MySQL function with query inside it

Issue

I used this mysql function to retrieve the sum of a column’s data with passing three parameters. Function return the sum of whole column regardless of where clause.
I mean that the query inside the function act as it has no where clause and this query works fine when used without function.
Here is the function

DELIMITER $$
CREATE FUNCTION calculate_customer_loan(customer_id INT, currency VARCHAR(10), type VARCHAR(10)) RETURNS DOUBLE
BEGIN
    DECLARE total DOUBLE;
    SELECT SUM(`amount`) INTO total FROM `deals` WHERE `customer_id` = customer_id AND `currency` = currency AND `type` = type;
    RETURN total;
END
$$
DELIMITER ;

Any idea!
help me.

Solution

You have a problem with parameter names matching column names. The column names win.

So, name your parameters to avoid ambiguity:

DELIMITER $$
CREATE FUNCTION calculate_customer_loan (
    in_customer_id INT,
    in_currency VARCHAR(10),
    in_type VARCHAR(10)
) RETURNS DOUBLE
BEGIN
    DECLARE v_total DOUBLE;

    SELECT SUM(d.amount) INTO v_total
    FROM deals d
    WHERE d.customer_id = in_customer_id AND
          d.currency = in_currency AND
          d.type = in_type;
    RETURN v_total;
END
$$
DELIMITER ; 

I removed the backticks. They are not needed and — more importantly — they do not distinguish between column names and variable names.

Answered By – Gordon Linoff

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0