Definition
A stored procedure, a database object executed on the Teradata Database, is a combination of SQL statements and control and condition handling statements that provide an interface to the Teradata Database.
- Procedure name
- Input and output parameters
- Procedure body.
For each stored procedure, the database includes a stored procedure table that contains the stored procedure body you write and the corresponding compiled stored procedure object code. Data dictionary tables contain stored procedure parameters and attributes.
Procedure Body and Source Text
The following terms are useful in understanding the structure of a stored procedure.
| Term | Definition |
|---|---|
| Procedure body | The set of statements constituting the main tasks of the stored procedure. The procedure body can be a single control statement or SQL statement, or a BEGIN … END compound statement (sometimes called a block). Compound statements can also be nested. |
| Source text | The entire definition of a stored procedure, including the CREATE/REPLACE PROCEDURE statement, parameters, procedure name, and the stored procedure body. |
Elements in a Procedure Body
A procedure body can contain the following elements:
| Stored procedure body of this type … | Can contain … |
|---|---|
| Single statement | one SQL statement or control statement, including dynamic SQL. The following elements are not allowed:
|
| Compound statement |
|
You do not have to enclose a compound statement within BEGIN … END keywords if the procedure body contains only one statement and does not contain any declarations.
Stored Procedure Benefits
A stored procedure provides control and condition handling statements, in addition to multiple input and output parameters and local variables, that make SQL a computationally complete programming language.
- Better performance because of greatly reduced network traffic between the client and server.
- Better application maintenance because business rules are encapsulated and enforced on the server.
- Better transaction control.
- Better application security by restricting user access to procedures rather than requiring them to access data tables directly.
- Better application execution because all SQL language statements are embedded in a stored procedure to be executed on the server through one CALL statement.
Nested CALL statements extend performance by combining all transactions and complex queries in the nested procedures into one explicit transaction, and by handling errors internally in the nested procedures.
Related Topics
- For more information about stored procedures, see SQL Stored Procedures.
- For information about stored procedure control statements, see SQL Control Statements.
- For information about external stored procedures, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 and Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
- For information on the syntax for creating stored procedures, see “CREATE/REPLACE PROCEDURE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.