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.