SQL - Stored procedures

About

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.

Articles Related

Benefits

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.

Management

List

See SQL - Schema (Metadata)