mysql Stored function not working

Question

I need to select the first flight of today. For that I'm using

delimiter//
CREATE function get_flightID(flightident())
returns int
begin
return (SELECT * FROM flightdep where depday = dayofweek(CURDATE()) ORDER BY depTime asc LIMIT 1;)
end//
delimiter;

But this isn't working. When I execute this I got an error about MySQL syntax.

17:50:50 ) end// delimiter Error Code: 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 ') end// delimiter' at line 1 0.000 sec

I'm really guessing what to use in my first line, I tried everything I could think of.

CREATE function get_flightID(flightident())

Why is flightident() in your function definition? It is not used anywhere in the body of the function. Take that out. — Mark Giaconia
– Mark Giaconia, Commented Mar 8, 2015 at 16:38

Gordon Linoff · Accepted Answer · 2015-03-08 16:57:14Z

1

First, you are returning select *, which would presumably have more than one column. Second, you have an unnecessary semicolon. Try something like this:

delimiter//
CREATE function get_flightID(flightident())
returns int
begin
    return (SELECT deptime
            FROM flightdep
            where depday = dayofweek(CURDATE())
            ORDER BY depTime asc
            LIMIT 1
           )
end//
delimiter;

answered Mar 8, 2015 at 16:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

O. Jones · Accepted Answer · 2015-03-08 17:08:31Z

0

You're attempting to return a resultset (that is, SELECT *...) from your function, but you declared it to return an int.

You want something like this:

DELIMITER $$
DROP FUNCTION IF EXISTS get_flightID $$
CREATE function get_flightID()
    returns int
    reads sql data
    begin
        declare result int;

        SELECT flightID INTO result
          FROM flightdep 
         where depday = dayofweek(CURDATE())
         ORDER BY depTime asc LIMIT 1;

         return result;
    end$$
DELIMITER ;

answered Mar 8, 2015 at 17:08

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Collectives™ on Stack Overflow

mysql Stored function not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related