What is the Syntax Error in this mySql script

Question

I have write a stored function :

  USE sakila;

DROP FUNCTION IF EXISTS sumOfFilm;

DELIMITER $$

CREATE FUNCTION sumOfFilm(
    mStore_id TINYINT, mMonth VARCHAR(4), mYear VARCHAR(4))
BEGIN
    DECLARE @sumOfFilm INT;
    SELECT COUNT(*) INTO @sumOfFilm FROM rental
    INNER JOIN inventory ON rental.inventory_id=inventory.inventory_id
    INNER JOIN store ON store.store_id=inventory.store_id
    WHERE mStore_id=store.store_id AND
            mMonth=month(rental_date) AND
            mYear=year(rental_date)
    GROUP BY store.store_id;
    RETURN @sumOfFilm;
END$$

DELIMITER ;

when I execute it, I get an error message:

ERROR 1064 (42000): 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 'BEGIN DECLARE @sumOfFilm INT; SELECT COUNT(*) INTO @sumOfFilm FROM rental INN' at line 3

please help me :)

http://stackoverflow.com/questions/18832005/mysql-stored-procedures-variable-declaration-and-conditional-statements — John Ruddell, Mar 18 '14 at 19:00
SET @sum = (SELECT COUNT(*) .... ) sorry forgot to add the () around the SET — John Ruddell, Mar 18 '14 at 19:50
You need a `RETURNS INT` after your parameters, since you are returning an INT (count). — Tricky12, Mar 18 '14 at 19:58

John Ruddell · Answer 1 · 2014-03-18T23:34:35.080

  USE sakila;

DROP FUNCTION IF EXISTS sumOfFilm;

DELIMITER $$

CREATE FUNCTION sumOfFilm(
    mStore_id TINYINT, mMonth VARCHAR(4), mYear VARCHAR(4))
BEGIN
    SELECT @sumOfFilm := COUNT(*) FROM rental
    INNER JOIN inventory ON rental.inventory_id=inventory.inventory_id
    INNER JOIN store ON store.store_id=inventory.store_id
    WHERE mStore_id=store.store_id AND
            mMonth=month(rental_date) AND
            mYear=year(rental_date)
    GROUP BY store.store_id;
    RETURN INT @sumOfFilm;
END$$

DELIMITER ;

also can try

SET @sumOfFilm = (SELECT COUNT(*) FROM rental)

What is the Syntax Error in this mySql script

1 Answers1