create procedure fails?

Question

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

I don't see "mydb" in your query? Are you quoting the full SQL statement? — Pekka, Jun 07 '10 at 14:36
@Pekka i omitted the 'use mydb;' line as it obviously isnt the error — Mark, Jun 07 '10 at 14:44
When omitting lines, amend the error output for the actual example accordingly :) — Wrikken, Jun 07 '10 at 15:11

Rich Adams · Accepted Answer · 2010-06-07T15:02:52.153

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.

sorry the first line is 'use mydb;' i omitted it as its obviously not the error. strangely i have just executed 'rollback' and then your code with the db change and it succeeded? — Mark, Jun 07 '10 at 14:43
@Mark I've updated with a new idea. This could explain why rollback caused it to work, as maybe that resets the delimiter. But as I've said in the edit, this is just a theory, I'm not in an environment where I can currently test this out to see. — Rich Adams, Jun 07 '10 at 15:06

score 0 · Answer 2 · answered Jun 07 '10 at 17:04

0

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

answered Jun 07 '10 at 17:04

George

319
3
4

surfmuggle · Answer 3 · 2021-07-02T14:43:22.433

I had the same problem using heidisql as the fronted to enter the SQL. My first attempt was:

CREATE PROCEDURE Add_Two (IN someNumber int, OUT result INT)
   BEGIN
     SELECT someNumber +2 INTO result;
   END

and this resulted in SQL ERROR (1064) because i was not aware that when using a client program a delimiter is needed to define the stored procedures.

After changing the above to this:

DELIMITER //
CREATE PROCEDURE Add_Two(IN someNumber int, OUT result INT)
   BEGIN
     SELECT someNumber +2 INTO result;
   END
//

It worked out.

Example to call it

 SET @someNumber :=8;
 CALL Add_Two(@someNumber, @result);
 SELECT @result;

create procedure fails?

3 Answers3