SQL - Stored procedures

Data System Architecture


A stored procedure is a compiled SQL program, consisting of one or more SQL statements, which resides and runs on the target database.

A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

A stored procedure is a precompiled collection of:

  • Transact-SQL (SQL Server),
  • PL-SQL (Oracle Database)
  • or other database procedural statements and optional flow control statements, similar to an executable script.

Stored procedures are stored and run within the database. You can run a stored procedure with the EXECUTE SQL statement in a database client tool, just as you can run SQL statements.

Unlike standard SQL, however, stored procedures allow:

  • user-defined variables,
  • conditional statements,
  • and other powerful programming features.

Not all databases support stored procedures, and stored procedure syntax varies depending on the database.

You might use stored procedures to complete the following tasks:

  • Check the status of a target database before loading data into it.
  • Determine if enough space exists in a database.
  • Perform a specialized calculation.
  • Drop and recreate indexes.

Database developers and programmers use stored procedures for various tasks within databases, since stored procedures allow greater flexibility than SQL statements.

Stored procedures also provide:

  • error handling
  • and logging necessary for critical tasks.

Developers create stored procedures in the database using the client tools provided with the database.


Stored procedures offer the following benefits:

  • They encapsulate code. The database operation appears once, in the stored procedure, and not multiple times throughout the application source. This improves debugging as well as maintainability.
  • Changes to the database schema affect the source code in only one place, the stored procedure. Any schema changes become a database administration task rather than code revision.
  • Since the stored procedures reside on the server, you can set tighter security restrictions. This saves more trusted permissions for the well-protected stored procedure themselves.
  • Because stored procedures are compiled and stored outside the client application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data.
  • Using stored procedures reduces network traffic.



See SQL - Schema (Metadata)

Discover More
Data System Architecture
A [[https://combostrap.com/frontmatter|frontmatter]] title shown on the Search Engine Result Pages

A [[https://combostrap.com/frontmatter|frontmatter]] description shown on the Search Engine Result Pages
Bobj Designer Wizard Stored Procedure
Business Object - Stored procedure

A stored procedure is a compiled SQL program, consisting of one or more SQL statements, which resides and runs on the target database. A stored procedure universe is a special universe that enables and...
Jdbc Class Architecture
JDBC - Callable Statement (Stored Procedure)

The CallableStatement objects interface adds methods to the statement interface for retrieving output parameter values returned from stored procedures. See java/sql/CallableStatementCallableStatement...
Card Puncher Data Processing
Oracle Database - DDL Locks (data dictionary lock)

A data dictionary (DDL) lock protects the definition of a schema object while an ongoing DDL operation (CREATE, ALTER, DROP) acts on or refers to the object. Only individual schema objects that are modified...
Card Puncher Data Processing
Oracle Database - Java Stored Procedure

Application developers familiar with procedural programming styles developed business application logic using languages like PL/SQL. The business logic they developed was deployed as stored program units...
Card Puncher Data Processing
Oracle Database - Stored Procedure

stored procedure in Oracle
Powercenter Transformation Store Procedure Import
PowerCenter - Stored Procedure Transformation

Stored procedure in the context of PowerCenter. Create a Stored Procedure transformation and name it SP_TransformationName. The Import Stored Procedure dialog box appears. Select the ODBC...
Relational Data Model
RDBMS - Relational Model

The relational model for database management: is a set of principles for relational databases formalized by Dr. E.F. Codd in the late 1960s. is a database model based on first-order predicate logic...
Data System Architecture
SQL - Objects

SQL Objects are the objects that may created in a database via the CREATE statement Then most known objects are: See
Data System Architecture
SQL - Table Function

PIPELINED functions (or table function) operate like a table and is a good workaround to Stored Procedures They are useful if there is a need for a data source other than a table in a select statement....

Share this page:
Follow us:
Task Runner