I was having some issues creating a (simple) stored procedure in phpMyAdmin. It seems that it has some issues supporting (or rather, not supporting) stored procedures. That said, there was a simple fix. 🙂
First off, my attempted code was this:
|
1 2 3 4 5 6 |
CREATE PROCEDURE spGetUsers() BEGIN SELECT UserName, FirstName, LastName FROM tblUsers ORDER BY LastName, FirstName; END |
Pretty simple and generic, right? Well, it gave me 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 ” at line 3
Not very helpful, but luckily there was an easy fix.
You’re probably worried that you have to change a bunch of things, or modify your query somehow but not to worry, all you have to do is change the delimiter to: //
That’s right just put // in the Delimiter box below the query text area and run it again. Success!
The reason this works is because you (may) have used the semi-colon in the query block inside the stored procedure. MySQL, unlike MSSQL, seems to take issue with this. If you take a look at the Create Procedure Documentation, you’ll see that they go through this same process to create a stored procedure in their example.
I learned a lot from this post, much appreciated! 🙂
Thanks a lot for posting this…it works
it doesnt works it gives error 1044 access denied for user
@shankar: That sounds like you don’t have permissions to create stored procs. Ensure that the user you’re using this under has the right privileges. For more information see the docs for Stored Routine Privileges.
Thank you… Love your post.. I do not have knowledge about stored procedure. Now i got it… thanks again for your clean explanation..
#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 ” at line 3 .
i got above error
Did you enter “//” in the delimiter box?