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.

Rename your function parameters. – Paul Spiegel Feb 23 '17 at 11:40 — Paul Spiegel, Feb 23 '17 at 11:40

Gordon Linoff · Accepted Answer · 2021-08-23T22:49:05.247

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.

edited Aug 23 '21 at 22:49

answered Feb 23 '17 at 11:41

Gordon Linoff

1,242,037
58
646
786

"The column names win" - I wouldn't say so: http://stackoverflow.com/questions/715229/mysql-stored-procedures-parameter-with-the-same-name-as-affectings-column-is – Paul Spiegel Feb 23 '17 at 11:46
@PaulSpiegel . . . That answer would appear to be incorrect. – Gordon Linoff Feb 23 '17 at 12:53
That answer seams to be correct. Here is a simple test: http://rextester.com/JEWVL30269 However I would still use different names. – Paul Spiegel Feb 23 '17 at 13:45
@GordonLinoff Isn't there a typo in this answer. You never actually set v_total. – Mike Aug 23 '21 at 20:21

MySQL function with query inside it

1 Answers1