Sometimes in your Database Admin career path, you’ve got to create a procedure to save your time and reduce loads of work on server. For example, everyday you need to run a report of how many customers have signed up for a newsletter, or you need to pull out a list of customers who purchased items for the past 12 hours… In these cases, you don’t want to re-write a SQL query every time; instead, you’ll create a procedure and store it in the Database Stored Procedure Library so that you can reuse it every day by calling it with a simple calling query such as Call GetListOfUser();

Procedure:
DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
SELECT * FROM customer where signup_date > ‘2012-01-26’;
END //
DELIMITER ;

Usage:
Call GetAllCustomers();

Advantages:
– Performance: Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog.
– Efficient: It runs faster than uncompiled SQL commands which are sent from application.
– Reduce traffic: Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.
– Reusable: Stored procedure is reusable and transparent to any application that wants to use it.
– Save time: Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions that are already supported in stored procedure.
– Security: Stored procedure is secured. Database Admin can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.

Disadvantages:
– Stored procedure make the database server high load in both memory and processors.
– Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the role of it.
– Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as PHP, Java, C#, C++…
– You cannot debug stored procedure in almost RDBMSs and in MySQL. There are some workarounds on this problem but it still not good enough to do so.
– Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase.

So when developing application you should balance between them to choose whether to use stored procedure or not.