A stored procedure is a collection of SQL statements assembled into a manageable block (also known as a Single Execution Plan) which is precompiled and stored in the database data dictionary. The stored procedure technique simplifies the database development process.

Stored procedure helps in accomplishing a steady execution of logic across programming languages and platforms. A block of SQL statements, with the needed business logic, can be written, analyzed and validated in a stored procedure to carry out a common job. Any application which requires to carry that common job can then merely execute the stored procedure. Moreover, the application does not need to compile and send the stored procedure at every execution. Writing business logic into a single stored procedure gives a common point of control to assure correct implementation of business rules.

Stored procedures are somewhat like user-defined functions (UDFs). The key distinction between them is that UDFs can be used like any other expression inside SQL statements, while stored procedures have to be executed using the CALL or EXECUTE statement.

[sourcecode language=”sql”]
CALL procedure_name()
[/sourcecode]

Or

[sourcecode language=”sql”]
EXECUTE procedure_name()
[/sourcecode]

A stored procedure can accept variables, return result-sets, and process the variables as when required. Result-set is a collection of database records produced as an outcome of a SELECT query.

Implementation

The precise and proper implementation of stored procedure differs from database to database. They can be implemented in many programming languages, like C, C++, C#, Java or Visual Basic. Some widely used implementation forms are Transact-SQL (Microsoft), PL/SQL (Oracle), SQL/PL (IBM DB2), SPL (Informix), PL/PGSQL (PostgreSQL), and PSQL (Firebird).

Advantages

  • Hidden business logic – Stored procedures can shelter applications from requiring to know the particulars of the tables and business logic within the database. The user should only know the stored procedure name, input parameters and the return datatype.
  • Precompiled storage and execution – Database server compiles the stored procedure once and then reutilizes the execution plan. This mechanism can remarkably improve performance when stored procedures are executed repetitively.
  • Decreased client-server transfer – Block of SQL statements are reduced to a single line of execution, thus reducing network data transfer between client and database server. This also increases availability of resources.
  • Reuse of code and business logic – Common stored procedure can be used by various applications and clients adhering to the same business logic.
  • Improved security – Various users/clients can be granted permissions on a stored procedure thus improving security control and management.

Example

Assume that a retail automation application is repeatedly executing the below query to validate available stock for various products.

[sourcecode language=”sql”]
SELECT Quantity FROM RetailInventory WHERE ProductID = ‘P1001’;
[/sourcecode]

Each time the retail automation application runs the above query, the database server is forced to read, analyze and compile it. Hence, it increases load on the server.

[sourcecode language=”sql”]
CREATE PROCEDURE sp_GetAvailableQuantity
@productId varchar(10)
AS
BEGIN
SELECT Quantity FROM RetailInventory WHERE ProductID = @productId;
END;
[/sourcecode]

Writing a stored procedure as above can considerably improve performance. Now, the retail automation application situated across multiple locations can access and execute the query. Also, the database server does not need to read, analyze and compile the stored procedure at each call.

[sourcecode language=”sql”]
EXECUTE sp_ GetAvailableQuantity ‘P1001’
[/sourcecode]

OR

[sourcecode language=”sql”]
EXECUTE sp_ GetAvailableQuantity ‘P1015’
[/sourcecode]