Syntax error in a create function, MySQL

Question

So, below is the function i'm writing(in MySQL) and I get a syntax error at the last "RETURN" line (RETURN d_count). I'm sure it's some simple thing, but I can't figure it out. Thanks!

DELIMITER $$
CREATE FUNCTION dept_count (dept_name VARCHAR(20))
    RETURNS INT DETERMINISTIC
    BEGIN
    DECLARE d_count INT;
    SELECT COUNT(*) into d_count
    FROM instructor
    WHERE instructor.dept_name=dept_name
RETURN d_count;
END $$

You need a semicolon betweeh the query and RETURN.

Barmar
– Barmar

2015-02-13 23:53:35 +00:00
Commented Feb 13, 2015 at 23:53 — Barmar
– Barmar, Commented Feb 13, 2015 at 23:53
EVERYTIME! thanks @Barmar you ROCK!

beau8008
– beau8008

2015-02-13 23:55:03 +00:00
Commented Feb 13, 2015 at 23:55 — beau8008
– beau8008, Commented Feb 13, 2015 at 23:55

souslov · Accepted Answer · 2015-02-13 23:54:06Z

2

You should separate SELECT and RETURN with semicolon ; :

DELIMITER $$
CREATE FUNCTION dept_count (dept_name VARCHAR(20))
    RETURNS INT DETERMINISTIC
    BEGIN
    DECLARE d_count INT;
    SELECT COUNT(*) into d_count
    FROM instructor
    WHERE instructor.dept_name=dept_name;
    RETURN d_count;
END $$

answered Feb 13, 2015 at 23:54

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

beau8008 Over a year ago

so, SELECT count(*) into d_count; and return d_count;

DeDee Over a year ago

WHERE instructor.dept_name=dept_name;, he marked it for you. You don't put semicolons in between a statement such as SELECT...FROM...WHERE.

beau8008 Over a year ago

i see it now, i thought he was taking about a different spot. sorry, very new to sql, and i thought it would be odd to put a semi colon in that spot.

Collectives™ on Stack Overflow

Syntax error in a create function, MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related