SQL Statements in Stored Procedures | Teradata Vantage - Executing a Stored Procedure - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

To execute a stored procedure, use the SQL CALL statement.

The CALL statement does not initiate a transaction.

Initiating a Transaction

Execution of the first SQL statement, other than a control statement, inside the stored procedure initiates a transaction. A control statement cannot initiate a transaction.
  • In Teradata transaction mode, each statement within the stored procedure is a separate transaction. You can explicitly initiate a transaction by specifying BT (BEGIN TRANSACTION) and ET (END TRANSACTION) inside the stored procedure body.
  • In ANSI transaction mode, unless the body of the stored procedure ends with a COMMIT, the actions of the stored procedure are not committed until a COMMIT or ROLLBACK occurs in subsequent statements.

    The request number is incremented for each SQL request inside the stored procedure.

Data Type Codes

The Teradata Database returns a specific set of CLIv2 data type codes to the calling application when the CALL statement is submitted.

Stored Procedure Parameters

The data type codes returned when the CALL statement is submitted include a parameter type. Stored procedure parameters can be of three types:
  • IN (input parameter)
  • INOUT (either input or output, or both)
  • OUT (output parameter)

Parameters of all data types are nullable in stored procedures.

Related Topics

For more information about:
  • Stored procedure execution, see the description of the CALL statement in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • Data type codes possible with a CALL statement, see “DataInfo Parcel” in Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 or Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417.
  • Memory considerations for INOUT parameters, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Executing a stored procedure from embedded SQL, see the CALL statement in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.