create procedure fails!?

Question

Hi, when trying to create a simple procedure in mysql 5.1.47-community it fails everytime i've tried everything! even simple things like this!

DELIMITER //
CREATE PROCEDURE two ()
begin
SELECT 1+1;
end;
//

The error is

ERROR: 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 'mydb' at line 1

Answer 1

The error message you've given doesn't correspond to the code you've pasted. You're referring to "mydb" somewhere in the SQL you're running yet it's not anywhere in the code you've put in the question.

The code you've given should work fine as I see no syntax errors, you may just need to give it a database to work on ("test" in my case here, perhaps it should be "mydb" for you?).

DELIMITER //
CREATE PROCEDURE test.two ()
begin
    SELECT 1+1;
end;
//

DELIMITER ;

CALL test.two;

However, I suspect the error you're getting is become of a line in your SQL that you're not showing us.

EDIT
It could perhaps be the delimiter command. You're changing the delimiter to // rather than the default ;. So perhaps you've run that command (and changed the delimiter for your session to //), and then tried to run USE mydb; where the ; is no longer recognised as a valid delimiter for your session, and that could be giving you the error. Try putting delimiter ; before your use line and see if that helps (and then use it again after you've defined your stored procedure so you can call it). This is just a theory though, as I'm not sure of the intricacies of the delimiter command.

Answer 2

Remove the final delimiter "end" instead "end;"