-1

I'm currently learning about SQL functions and have encountered a problem I don't know how to solve. So I'm supposed to create a function that has three parameters, a month, year and amount. It's supposed to return a count of every payment amount above the given amount and on the given date. It looks something like this now:

CREATE FUNCTION sakila.my_report(month INT, year INT, amount INT)
    RETURNS INT

    BEGIN
        DECLARE @count INT;
        DECLARE @date DATE;
        SET @date = cast(@year + '-' + @month + '-00' as date);
        SELECT @count = COUNT(*)
        FROM payment
        WHERE payment_date = @date AND payment.amount > @amount;
        RETURN(@count);
    END //
DELIMITER ;

This returns an error however.

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@count INT; DECLARE @date DATE; SET @date = cast(@year + '-' +' at line 5
Chris Albert
  • 2,462
  • 8
  • 27
  • 31
Murkyu
  • 1

1 Answers1

1

You confuse user-defined variable (name starts with @, cannot be declared, have no definite datatype, connection-specific) and local variable (name not starts with @, must be declared, have specific datatype, codeblock-specific).

Also you use keywords as local (procedure/function parameter) variables names.

Remove all @ chars. Rename variables.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Upvoted - but FWIW using keywords as variables is allowed unless they are _reserved_ keywords. None of the variables in the OP's question conflict with reserved keywords. – Bill Karwin Mar 16 '22 at 19:59
  • @BillKarwin *using keywords as variables is allowed unless they are reserved keywords.* This is true. But there have already been cases when the keyword in the next version became reserved word which causes a lot of problems. If you don't use keywords as object names then you won't meet with such unpleasant situation. – Akina Mar 16 '22 at 20:14
  • Do you have an example of a non-reserved keyword which became a reserved keyword in a later version? I'm not denying one exists, I just haven't checked. I can't think of one. – Bill Karwin Mar 16 '22 at 20:20
  • @BillKarwin See [MySQL 8.0 Reference Manual ... Keywords and Reserved Words](https://dev.mysql.com/doc/refman/8.0/en/keywords.html). Reserved -> non-reserved - 7 keywords, non-reserved -> reserved - 4 keywords. – Akina Mar 16 '22 at 20:55
  • I understand there are new keywords added in every release. Som are reserved and some are non-reserved. I'm asking for an example of a non-reserved keyword in 5.7 that became reserved in a later version. – Bill Karwin Mar 16 '22 at 21:19
  • 1
    @BillKarwin *I'm asking for an example of a non-reserved keyword in 5.7 that became reserved in a later version.* I gave you the link! `CUBE`, `FUNCTION` - became reserved in 8.0.1; `ROW`, `ROWS` - became reserved in 8.0.2. These keywords were not reserved in 5.7. – Akina Mar 17 '22 at 04:16
  • Thank you, that was what I was asking for. – Bill Karwin Mar 17 '22 at 04:23