CODEWITHSUNDEEP
mysql
Yassine ECHCHARAFI
Yassine ECHCHARAFI

Reputation: 660

SQL Error: 1064 You have an error in your SQL syntax;

i have a problem with the next code:

create function proc1 (id int)
returns float
begin
    declare sum float
    select sum = (note1+note2+note3)/3 from test1.note where note.id=id;
    return sum;
end

I got this error :

#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 'select sum = (note.note1+note.note2+note.note3)/3 from test1.note where note.id=' at line 5

I've spent much time searching for a solution but no solution was found :(

Upvotes: 0

Views: 8354

Answers (2)

robsn
robsn

Reputation: 744

Use set instead of select or better return without declaring a variable:

delimiter //
create function proc1 (id int)
returns float
begin
    return (select (note1+note2+note3)/3 from test1.note where note.id=id);
end
//
delimiter ;

or with variable:

delimiter //
create function proc1 (id int)
returns float
begin
    declare sum float;
    set sum = (select (note1+note2+note3)/3 from test1.note where note.id=id);
    return sum;
end
//
delimiter ;

Upvotes: -1

juergen d
juergen d

Reputation: 204756

delimiter |
create function proc1 (id int)
returns float
begin
    declare sum float;
    select (note1+note2+note3)/3 into sum from test1.note where note.id=id;
    return sum;
end
|
delimiter ;

You had multiple errors:

  • no delimiter defined. Otherwise the DB thinks your function definition stops at the first ; which is wrong
  • you need to use select ... into. Otherwise you get a "not allowed to return a resultset from a function" error
  • you forgot a semicolon after your declare

Upvotes: 2

Related Questions