Stored Procedure

Definition - What does Stored Procedure mean?

A stored procedure is a subroutine available to connected relational database system applications. Stored procedures must be called or invoked, as they are sets of SQL and programming commands that perform very specific functions. Most major relational database systems (e.g., SQL Server, Oracle, MySQL, Postgres and others) provide support for stored procedures.

This term is also known as proc or storedproc.

Techopedia explains Stored Procedure

Stored procedures are used when an application needs to perform a complex task using relational database information. An example might be a loan loan application to determine a customer’s repayment ability and creditworthiness. To check the customer’s repayment ability, the loan officer compares the customer’s average monthly income to his monthly account withdrawal sum over a 24-month period. To verify creditworthiness, the loan officer submits the customer’s ID or social security number to a credit reporting website.

Both actions above are complex and difficult to achieve using basic SQL commands. In addition, the customer loan approval process may be performed at varying times for different customers (i.e., the same action is repeated several times), but different customer information is associated with each action.

The above situation is an ideal example of when storedprocs should be used--i.e., when performing a complex action using a combination of SQL, Procedural Language/Structured Query Language (PL/SQL) or external programming language, e.g., Java or C++. Second, the same action is performed repeatedly, and the only changes are the parameters or data to be processed.

Stored procedures usually provide a performance benefit over writing application code, for the following two reasons:

  • They do not incur extra inter-program communication between the database and external application.
  • Do not need to be compiled and executed for each instance, as storedprocs are compiled only once.

Stored procedures are stored as part of the database’s data dictionary, rather than the application that references the database. When storedprocs call other storedprocs, this is known as a setup of nested stored procedures.

Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.