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:
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:
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:
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:
Developers create stored procedures in the database using the client tools provided with the database.
Stored procedures offer the following benefits: