CODEWITHSUNDEEP
mysqlstored-functions
Karen
Karen

Reputation: 13

stored function returns 0 mysql

I make a this stored function to return the amount of invoices per customer:

delimiter //
CREATE FUNCTION function1(id INT) RETURNS INT READS SQL DATA 
BEGIN 
    DECLARE result INT;
        (SELECT count(invoice_id) INTO @result FROM invoices WHERE customer_id = @id);
    RETURN @result;
END//
delimiter ;

but when I use it, returns 0:

SELECT function1(12) AS Q;

and the query returns 428 :

SELECT count(invoice_id) AS Q FROM invoices WHERE customer_id = 12;

I need to know what am I doing wrong.

Upvotes: 1

Views: 45

Answers (1)

nbk
nbk

Reputation: 49375

@id is not the same as id

But it id better to use variable names _id to differentiate variables from column names

delimiter //
CREATE FUNCTION function1(_id INT) RETURNS INT READS SQL DATA 
BEGIN 
    DECLARE result INT;
        (SELECT count(invoice_id) INTO @result FROM invoices WHERE customer_id = _id);
    RETURN @result;
END//
delimiter ;

Upvotes: 1

Related Questions