Using phpMyAdmin to Create Stored Procedures
I spend a good portion of each day writing stored procedures in MySQL. Here’s a simplified example of a procedure I might write:
DELIMITER $$ DROP PROCEDURE IF EXISTS spFoo $$ CREATE PROCEDURE spFoo () BEGIN SELECT 'Foo' FROM DUAL; END $$ DELIMITER ;
If you were to copy and paste this code into phpMyAdmin’s SQL editor and execute it, just as it is, you would get the following 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 ‘DELIMITER’ at line 1
I’ve seen this error posted to numerous forums, but I’ve noticed that there are few places where new users would find the actual solution to this problem. So, here it is.
- Open phpMyadmin.
- Select a database to work with.
- Open the SQL tab.
- Select all of the SQL statements between the DELIMITER statements in your stored procedure script. Do not include the DELIMITER statements! Here’s what my example script should look like:
DROP PROCEDURE IF EXISTS spFoo $$ CREATE PROCEDURE spFoo () BEGIN SELECT 'Foo' FROM DUAL; END $$
- In the delimiter field, just below the SQL editor’s text area, enter $$ as your delimiter.
Here are some answers to your potential comments:
- Q: Why don’t you just copy and paste your code to the command line?
- A: If the stored procedure script is exceptionally long, this can be messy.
- Q: Why don’t you just use the MySQL Query Browser that comes with the MySQL GUI Tools?
- A: When you have a large number of tables in your database, the schema refresh in the MySQL Query Browser is painfully slow. If anyone has a good solution to that problem, please post the answer below in the comments!
- Q: What is phpMyAdmin?
- A: phpMyAdmin is a web-based interface that lets users manage MySQL databases right on the web. Check it out at: www.phpmyadmin.net.