A Stored procedure is a subroutine written in the computer language. Large and complex SQL statements could be defined as a Stored Procedure (SP) and executed using the CALL PROCEDURE or EXECUTE PROCEDURE statements. Stored procedures encapsulate set of queries to execute on a database server. SPs are defined once but used many times.

Stored Procedures are set of SQL commands compiled and stored in the database server. SP could be called to return the result set the stored procedure is intended to return again and again without compiling again. We could pass a variable to the stored procedure and receive a result set or modified variable. The result sets from a stored procedure could be processed by cursors. IF, WHILE, LOOP, CASE, REPEAT are some example of stored procedure statements.

The stored procedures are similar to used defined functions UDFs but there are some differences between procedures and functions.
a) Function needs to return a value; procedure can return or need not return any value.
b) Function can have only IN parameters, Procedure can have IN parameters or IN OUT parameters
c) Function is a subprogram written to perform certain computations and return only one value; Procedure is a subprogram written to perform set of actions and can return one or more values or none.

The database vendors had come up with stored procedure packages for each of their database like Oracle with PL/SQL, Microsoft SQL Server with Transact-SQL, DB2 uses SQL/PL.

Benefits:
• Stored procedure improves efficient use of CPU or network utility.
• Store procedure reuses the compiled programs again and again whenever called.
• It runs concurrently with the same efficiency as it need not compile each time

Application of stored procedure:
You have an inventory system and the store manager needs to decrement one item from the inventory whenever there is shipment. In this case a stored procedure to be triggered automatically decrement the inventory item whenever a shipment record is entered into the database would be very efficient.

Related posts:

1. What is BLOB BLOB (Binary Large Object) is a large object data type...
2. What is ODBC What is ODBC ? ODBC abbreviation for Open Database Connectivity...
3. Transactional Database Transactional Database has the functionality or an important role required...
4. Data Dictionary Data Dictionary is an organized file of details about the...
5. What is SQL SQL is the computer language to manage data in the...