Introducing to Stored Procedure

Stored procedure by definition is a segment of code which contains declarative or procedural SQL statement. A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from a program.

As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE, ALTER TABLE and etc. Furthermore, a stored procedure also supports procedure statements such as IF, WHILE to make it as powerful as a normal programming language.

Using Stored procedure has several advantages :

• It is used to increases the performance of application because when we create stored procedure, they are compiled and stored in database catalog. Later when applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the applications.
• The network traffic between application server and database server is also signification reduced because the applications don't have to send such long and uncompiled SQL statements to the server to get the data back.
• Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.
• One of the most advantage of stored procedure is code reusability. Once created, a store procedure can be reused over and over again by multiple applications.

It is the best to illustrate the ability of stored procedure by showing examples, you can follow via this tutorial to understand more about stored procedure. We will use Microsoft SQL Server to demonstrate stored procedure, you can also use MySQL with a change a little bit because of specification of each database server is different. Continue reading on getting started with stored procedure