Technorati Profile

Why to use Procedures in MySQL

I heard this question many times from all developers which I tried to convince them to use procedures and upgrade their versions from mysql-4 to mysql-5.

They used to put their sql statements in their php pages, and then transfer the whole query to MySQL server and waiting for the response to display the result, what a way?! So I decided to make a session for all these developers to describe to them why we have to use procedures, and this is some brief points that I came with:

First, procedures are pre-complied objects so there’s no need to compile the queries each time the page called, which will increase the performance.

Let’s assume that we have a query repeated in 100 pages, so to make just a simple change such as adding a column in the selected fields or add a condition in the where clause, then you have to go through all these pages and apply these changes, that will take a long time for searching, applying, and testing, but when we using procedures the query will be in database side and it takes you to change it once, so it easier to change also.

Procedure definitely far secure than embedded queries which can interrupt by sql injection, because the procedure has already complied in the building stage, so the variables defined already and can’t have sql injection inside these variables.

I found that MySQL procedures are more reliable than oracle procedures and that because oracle build the procedure even if there was syntax error but of course gives you a warning and set the status of the procedure is invalid. Mean while MySQL strict in the right syntax and can’t accept the errors in their procedures.