Blog

Friday, Jul 17, 2009 Tips for those who are new to MySQL stored procedure programming

Thanks to my early years in web application development. I am fairly familiar with MySQL syntax and SQL syntax.

In the old days (which is more than 2 or 3 years ago…), we always need to write lengthy SQL statements in PHP. With stored procedure, we can return many of those length PHP level SQL scripts back to MySQL layer.

I am recently involved in a project which I have to write some stored procedure. Man, it’s been some time since I last work on stored procedure. Well…, as expected, I forget all the syntax and details….

Here’s some useful tips, I think, to those who are familiar with SQL but not the details of stored procedure…

1. dynamic SQL – an example from the O’Reilly book – MySQL Stored Procedure Programming. Pay attention to the “@” sign in the “USING” clause.
2. more details on PREPARE syntax
3. cursor and handler
4. reference to other useful syntax – flow control, variables…

Some more tips:

• check for SQL syntax error for your dynamic statement. You can run the query in PHPmyadmin first.
• remember to close your “IF” statement with “END IF”