Stored Procedures with PhpMyAdmin

At first sight, MySQL with PhpMyAdmin looks very cheap in comparison to for example Microsoft SQL Server and the SQL Client. Actually MySQL can be more complex as it looks like. So I learned recently, that MySQL supports stored procedures. The only problem is, that PhpMyAdmin does not support them. You can create it with PhpMyAdmin, but there are no menu to view or edit them.

View stored procedures

PhpMyAdmin has no menu for stored procedures. But you can view it with the help of SQL. There are SQL commands to view all stored procedures and also their code:

  • this statement returns a list of all stored procedures that are visible by the current user
  • you can filter this list for only one database
  • with the like operator you can also filter for strings

If you know the name of a stored procedure you can also get the create statement of it. This can be done with the following statement:

A simple stored procedure

Let me first explain our problem: we have a table called books. There we have a primary key (in_id), a column for the EAN code of the book (in_EAN) and a column that shows us if this book is written in german (in_DE). Our import does not contain this information, so we have to get it from another column. The 4. char from the EAN code is this peace of information. If this char is 3, then it is a german book.

To fill this column (in_DE) we have to compute some code. This can be done with update and insert stored procedures. This procedures are automatically fired on every update or insert on this table. Here is the code for the update stored procedure:

If you copy paste this into the SQL textarea of PhpMyAdmin and run it, you will get an error.

The problem with the delimeter

Why do we get an error? The statement looks ok? After some search I found the solution in any forum. SQL statements are normally one line long and they and with a ‘;’. Our statement is much longer and it has an ‘;’. But not at the end. So the SQL interpreter only gets the code up to to ‘;’ and so our END is missing.

The solution is quite simple. In the bottom of the SQL textarea, there is a small input field called delimeter. There you see a ‘;’ per default. Here we have to define another sign (for example //). The correct statement looks as follows:

And the Insert stored procedure:

More information can be found at finden.

Who else had troubles with stored procedures?

(Visited 5,824 times, 1 visits today)

4 Responses

  1. rony says:

    it’s not working… still has error

  2. Annapurna agrawal says:

    “The only problem is, that PhpMyAdmin does not support them. You can create it with PhpMyAdmin, but there are no menu to view or edit them.”
    We can view the stored procedure for a particular db from the routines tab.

  3. praba says:

    how to debug stored procedure in phpmyadmin

    • Werner Ziegelwanger says:

      you can run sql commands in SQL Editor in phpmyadmin. You can replace params with fix values. If you don’t know why your stored procedure don’t work in a special case, you may have to look into mysql logs for SQL errors.

Leave a Reply

Your email address will not be published. Required fields are marked *