how to solve syntax error in procedure in mysql?

Question

i am executing a procedure in mysql, procedure is-

delimiter $$
create procedure getFacility (in id int, out MyFacility VARCHAR(200))
begin
    select Facility into MyFacility 
        from facilities 
        where FacilityID = id ;
end $$
delimiter ;

and it is giving error below-

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 ms taken)

how to solve this error?

Thanks,
Yugal

Are you executing the query against MySql directly? or are you using a application connector like jdbc? — The Scrum Meister
– The Scrum Meister, Commented Feb 21, 2011 at 6:49
Try changing your delimiter to some other single character like | instead of $$ — Sachin Shanbhag
– Sachin Shanbhag, Commented Feb 21, 2011 at 6:51
i am executing query against MySql directly. i changed delimeter to | insteed of $$, but now also it is giving error.now what to do? please suggest me. — Yugal
– Yugal, Commented Feb 21, 2011 at 7:12
i have put "END$$" but it is giving same error. Am i missing somthing? — Yugal
– Yugal, Commented Feb 21, 2011 at 7:28

Sachin Shanbhag · Accepted Answer · 2011-02-21 07:23:04Z

1

I think the error is because of the space between END and $$. Try END$$ and it should compile properly.

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

answered Feb 21, 2011 at 7:23

Sachin Shanbhag

55.8k11 gold badges92 silver badges103 bronze badges

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

2 Comments

Sachin Shanbhag Over a year ago

@Shooter - Did this help you resolve your issue? In comment you have mentioned saying END$$ gives you same error?

Yugal Over a year ago

i put DELIMETER $$; on the top and END; $$ DELEMETER; $$ at the end of procedure, now it is working. Thanks -

Adam Morgan · Accepted Answer · 2013-06-21 19:38:19Z

1

i had the same issue... my solution was to just remove the delimiter operator call on line one, and remove the delimiter after 'END'... it seems JDBC just doesn't like that operator.

it would also be really nice if MYSQL gave some more precision on syntax errors.

answered Jun 21, 2013 at 19:38

Adam Morgan

5051 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

how to solve syntax error in procedure in mysql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related