Learning MySQL 5 Stored Procedures

I recently have been trying to pick up Stored Procedure in MySQL 5. It has been pretty straight forward to learn if you already have done any programming before. Just a matter of familiarizing yourself with the syntax.

If you are learning Stored Procedure from scratch just like me, here are a few gotchas I’ve found:

• DECLARE statements *must* come at the top of each procedure.
• Each procedure is tied to a database. Therefore, you must specify the database you want to create procedures for by executing USE database_name before you start working. The same goes with calling the procedures: you either execute USE database_name or call the procedure by prefixing it with the database name (ie, CALL database.procedure()).
• To those who use replication, yes, stored procedures get replicated to the slaves as well.
• Avoid using variable names that are actually SQL keywords such as DATE. I haven’t read any documentation that explicitly says not to use the keywords, but I did run into errors when I tried to use one.
• MySQLi extension is required to call stored procedures from PHP. This is a big consideration you need to account for if you work on shared hosts that do not have MySQLi (which in turns need PHP 5 and MySQL 5). You will get this error “PROCEDURE XXX can’t return a result set in the given context” if you attempt to execute a stored procedure with the good ol’ “mysql” extension.

Of course, I have some tips for you as well:

• You can use any text editor to write the procedures, but MySQL’s own Query Browser actually works pretty well as an editor for stored procedures. Once a procedure is crated, you can see it by expanding the database tree on the right. Try it.
• To dynamically generate SQL statements in procedures, you will need to concatenate different parts of the statement together and send it in as an Prepared Statement.

I hope this blog can save headaches for some of you. By the way, I ran into problems while re-compiling PHP for MySQLi support. I will provide my solution soon so stay tuned.

Advertisement