Introduction
“ A stored routine is a set of SQL statements that can be stored in the server.”
A stored procedure is a method to encapsulate repetitive tasks. They allow for variable declarations, flow control and other useful programming techniques.

The “academic” position on this is quite clear and supports the extensive use of stored procedures. On the other hand, when you consider the opinions of those who work with them day in, day out, you’ll notice that reactions vary from complete, unwavering support to utter hate. Keep these in mind.

Pros
* Share logic with other applications. Stored procedures encapsulate functionality; this ensures that data access and manipulation are coherent between different applications.

* Isolate users from data tables. This gives you the ability to grant access to the stored procedures that manipulate the data but not directly to the tables.
* Provide a security mechanism. Considering the prior item, if you can only access the data using the stored procedures defined, no one else can execute a DELETE SQL statement and erase your data.
* To improve performance because it reduces network traffic. With a stored procedure, multiple calls can be melded into one.

Cons
* Increased load on the database server — most of the work is done on the server side, and less on the client side.
* There’s a decent learning curve. You’ll need to learn the syntax of MySQL statements in order to write stored procedures.
* You are repeating the logic of your application in two different places: your server code and the stored procedures code, making things a bit more difficult to maintain.
* Migrating to a different database management system (DB2, SQL Server, etc) may potentially be more difficult.

The tool that I am working with in this tutorial, MySQL Query Browser, is pretty standard for database interactions. The MySQL command line tool is another excellent choice. I make note of this because the popular phpMyAdmin doesn’t support stored procedure execution.

Step 2 – How to Work with a Stored Procedure
Creating a Stored Procedure
view plaincopy to clipboardprint?

1. DELIMITER //
2.
3. CREATE PROCEDURE `p2` ()
4. LANGUAGE SQL
5. DETERMINISTIC
6. SQL SECURITY DEFINER
7. COMMENT 'A procedure'
8. BEGIN
9. SELECT 'Hello World !';
10. END//

DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END//

The first part of the statement creates the procedure. The next clauses defines the optional characteristics of the procedure. Then you have the name and finally the body or routine code.

Stored procedure names are case insensitive, and you cannot create procedures with the same name. Inside a procedure body, you can’t put database-manipulation statements.

The four characteristics of a procedure are:
* Language : For portability purposes; the default value is SQL.
* Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
* SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER.
* Comment : For documentation purposes; the default value is ""

Calling a Stored Procedure
To call a procedure, you only need to enter the word CALL, followed by the name of the procedure, and then the parentheses, including all the parameters between them (variables or values). Parentheses are compulsory. view plaincopy to clipboardprint?
1. CALL stored_procedure_name (param1, param2, ....)
2.
3. CALL procedure1(10 , 'string parameter' , @parameter_var);


Modify a Stored Procedure
MySQL provides an ALTER PROCEDURE statement to modify a routine, but only allows for the ability to change certain characteristics. If you need to alter the body or the parameters, you must drop and recreate the procedure.
Delete a Stored Procedure
view plaincopy to clipboardprint?
1. DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS clause prevents an error in case the procedure does not exist.

Full article...