MySQL function with query inside it

Question

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.

• Paul Spiegel: Rename your function parameters.

Answer 1

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.

• Paul Spiegel: "The column names win" - I wouldn't say so: stackoverflow.com/questions/715229/…
• Gordon Linoff: @PaulSpiegel . . . That answer would appear to be incorrect.
• Paul Spiegel: That answer seams to be correct. Here is a simple test: rextester.com/JEWVL30269 However I would still use different names.
• Mike: @GordonLinoff Isn't there a typo in this answer. You never actually set v_total.