Can someone please explain a basic mysql stored procedure and outline the benefits of using this?

Stored procedures are a feature of most modern databases (Sybase, MS SQL Server, Oracle, MySQL etc). They allow you to put a whole bunch of logic (if then else, loops etc) in with your SQL and then run it with a single call to the database. The advantages of stored procedures are:

Less code: The advantage is that we can now have just a single call rather than a whole bunch of complicated SQL appearing in the calling program (probably PHP for MySQL). If you imagine a program that uses the database a lot, then removing all the code from a program that wasn't SQL, what's left would be similar to the contents of your stored procedure.

Easier to understand: If you saw a bit of code saying call add_new_user('fred') then that would be far easier to understand than 2 pages of embedded SQL that added data to various tables and passed data back and forth between the database and the calling program.

Faster to write: Because you can write each procedure individually without tonnes of supporting code passing data back and forth between the calling program and the database. Once you have your procedure then you will use it throughout your program rather than rewrite a whole bunch of similar embedded sql all over again. This saves time.

Better testing: Each stored procedure can be easily tested by itself within the database. Debugging a program with embedded SQL means 1/3 of the time you're testing the SQL, another 1/3 is testing the logic in the calling program and the final 1/3 is testing the interface between the database and your program. Also the same procedure will now get called lots of times so it will get tested more often than lots of embedded code that's distributed across your programs.

Less errors: Less code and more testing means fewer bugs.

Easier to split the work: On larger applications you can give the stored procedures to a database specialist rather than to a normal 3GL programmer who may know a little about SQL. This means better code all round as the SQL specialists write the SQL and the 3GL programmers can concentrate on the look and feel of the web application etc.

Performance: Stored procedures are also more efficient and easier to tune.

Mike

There was some initial resistance to stored procedures in MySQL simply because they were new and MySQL coders weren't sure what to do with them.