Restrictions on Stored Procedures | Teradata Vantage - Stored Procedure Lexicon - 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™

Names

The names of stored procedures, as well as stored procedure parameters, local variables, labels, for-loop correlation names and columns, cursors, and for-loop variables must be valid Teradata SQL names (or identifiers).

All rules applying to naming a database object also apply to naming a stored procedure.

The following rules apply to specific names in stored procedures:

This name … Must be unique in …
correlation or column a FOR iteration statement, or a DECLARE CURSOR statement.

The same correlation or column name can be reused in nested or non-nested FOR statements within a stored procedure.

A correlation or column name can be the same as the for-loop variable and cursor names in a FOR statement.

cursor nested FOR statements.

A cursor name can be the same as the for-loop variable and correlation or column name in a FOR statement.

In cursors defined using DECLARE CURSOR, a cursor name must be unique in the compound statement in which it is declared.

for-loop variable nested FOR iteration statements.

A for-loop variable name can be the same as the cursor name and correlation or column name in a FOR statement.

label nested iteration statements or a group of nested BEGIN … END statements. Label names of iteration statements can be reused with other non-nesting iteration constructs or non-nesting BEGIN … END statements in a stored procedure.

Labels have their own name space, so they do not interfere with other identifiers used for local variables and parameters.

local variable the BEGIN … END compound statement in which it is declared.
parameter a stored procedure.

For example, a parameter name cannot be repeated for a local variable in the same stored procedure.

stored procedure a database since it falls in the name space of tables, macros, views and triggers.

Keywords

Keywords in stored procedures are not case-sensitive. Uppercase and lowercase can normally be used interchangeably.

You can use more than one blank space character between syntax elements to increase readability, but multiple sequential blank space characters are treated as a single space.

Literals

All Teradata Database-supported literals for directly specifying values in the text of an SQL statement, including control statements, are valid in stored procedures.

Local Variables

You can specify local variables of any Teradata Database-supported data type in the variable declaration statements within a BEGIN … END compound statement of the stored procedure.

A compound statement can have multiple variable declarations, and each DECLARE statement can contain multiple local variables.

A local variable can have any valid data type.

If a local variable is specified in an SQL statement other than a control statement, it need not be prefixed with a colon character (:). The colon prefixed to a local variable is still supported, but is not recommended.

If a local variable is not prefixed with a colon character, the variable name should not be the same as a column name.

If an SQL statement contains an identifier that is the same as an SQL variable name and a column name, Teradata interprets the identifier as a column name. To prevent this, the SQL variable identifier that is a column name should be qualified with the compound statement name.

A local variable name cannot be any of the following names reserved for status variable names:
  • SQLCODE
  • SQLSTATE
  • ACTIVITY_COUNT

A DEFAULT clause, if specified for a local variable, can contain a literal. Expressions are not allowed.

Local variable can be qualified with the label of the corresponding compound statement in which the variable is declared. This helps avoid conflicts that might be caused by reused local variables in nested compound statements.

Parameters

A stored procedure can have up to 256 parameters of any Teradata Database-supported data type and character.

Stored procedure parameters and their attributes are stored in the DBC.TVFields table of the data dictionary.

If a parameter is specified in an SQL statement other than a control statement, it need not be prefixed with a colon character (:). The colon character prefix to a parameter is supported, but not recommended.

If a parameter is not prefixed with a colon character, it should not be the same as a column name.

If an SQL statement contains an identifier that is the same as an SQL parameter and an column name, the Teradata Database interprets it as a column name. To prevent this, you should qualify the column name with the compound statement name.

The following three names are reserved for status variables and cannot be used for parameters:
  • SQLCODE
  • SQLSTATE
  • ACTIVITY_COUNT
The following clauses cannot be specified for parameters:
  • DEFAULT
  • FORMAT
  • NOT NULL

Rules for IN, OUT, and INOUT Parameters

Parameter Allowed In Not Allowed In
IN Source specification of an SQL statement Target specification of an SQL statement.
OUT Target specification of an SQL statement Source specification of an SQL statement.
INOUT Source and target specifications of an SQL statement.

Parameters can have any valid data type.

INOUT parameters can be used for both input and output values.
  • You can specify an input value as an argument for the INOUT parameter while executing the stored procedure.
  • You can read the output value from the same parameter after execution of the procedure.

The maximum data size of all input and all output parameters in a CREATE/REPLACE PROCEDURE is 1 MB.

When you invoke a stored procedure, the IN constants assume the data type of the value specified unless overridden in the CALL statement.

The data type for an INOUT constant argument is governed by the data type of the value passed in, not what is defined. If the data type of the value passed in is smaller than the data type defined in the CREATE/REPLACE PROCEDURE statement, and the stored procedure returns a value larger than the maximum value of the data type for the value passed in, the system returns an overflow error.

For example, consider a stored procedure that defines an INTEGER INOUT parameter. If you call the procedure with a constant input value that fits into a SMALLINT, the system returns an overflow error if the output value is larger than 32767, the maximum value of a SMALLINT.

Labels

You can use a label with iteration statements (FOR, LOOP, REPEAT and WHILE) and BEGIN … END compound statements in a stored procedure. The following rules apply:
  • A beginning label must be terminated by a colon character (:).
  • An ending label is not mandatory for an iteration statement or compound statement.

    If an ending label is specified, it must have a corresponding beginning label associated with that iteration or BEGIN … END statement. For example, an ending label following an END WHILE must have an equivalent beginning label and colon character preceding the corresponding WHILE.

  • The scope of a label is the iteration statement or BEGIN … END compound statement with which it is associated.

    This implies that if another iteration statement or compound statement is nested, the label name associated with the outer iteration or compound statement must not be used with any inner iteration statement(s) or compound statement(s).

FOR-Loop Variables

A FOR-loop variable is normally used as the name for a FOR iteration statement.

A FOR-loop variable must be used to qualify references to correlation or column names. If not qualified with the for-loop variable name, the correlation or column name references in SQL statements, including control statements, are assumed to be parameters or local variables.

The following rules apply:
  • When used in an SQL statement other than a control statement, a for-loop variable must be prefixed with a colon character (:).
  • The scope of the for-loop variable is confined to the FOR statement with which it is associated.

    In the case of nested FOR statements, the for-loop variable associated with an outer FOR statement can be referenced in other statements inside the inner FOR statement(s).

Cursors

See SQL Cursors for rules and guidelines governing the use of cursors in stored procedures.

See DECLARE CURSOR (Stored Procedures Form) and FOR for more details and examples of cursors use within stored procedures.

Correlation and Column Names

The columns in the cursor specification of a FOR statement or DECLARE CURSOR statement can be aliased using an optional keyword AS.

The ANSI/ISO SQL standard refers to aliases as correlation names. They are also referred to as range variables. The following rules apply:
  • An expression used in the cursor specification must be aliased.
  • The data type (including the character data type CHARACTER SET clause) of a correlation name or column is the data type of the corresponding correlation name or column in the cursor specification.
  • An correlation name or column must be referenced in the body of the FOR iteration statement by qualifying it with the associated for-loop variable name. An unqualified name is assumed to be a local variable or a parameter name.
  • The scope of a correlation name or column in a FOR iteration statement is the body of the FOR statement.

    In the case of nested FOR statements, a correlation name or column associated with an outer FOR statement can be referenced in statements inside inner FOR statements.

  • Correlation names or column names used in an SQL statement other than a control statement must be prefixed with a colon character (:) when used in a stored procedure.

Supported Data Types

All data types supported that Teradata Database supports can be used for stored procedure parameters and local variables, including the JSON data type, UDTs (except VARIANT_TYPE UDTs), BLOBs, and CLOBs.

For correct operation of a UDT within a stored procedure, the UDT must have the mandatory ordering and transform functionality defined In addition, the tosql and fromsql transform routines must be backed up with an equivalent set of predefined-to-UDT and UDT-to-predefined implicit cast definitions. The easiest way to do this is to reference the same routines in both the CREATE TRANSFORM and CREATE CAST statements.

For a distinct UDT, you can use its system-generated default transform and implicit casting functionality.

For a structured UDT, however, you must explicitly define the functionality using CREATE TRANSFORM and CREATE CAST statements.

KANJI1 support is deprecated. KANJI1 is not allowed as a default character set. The system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible.

TD_ANYTYPE parameter data type cannot be used with SQL stored procedures.

Users may create a Teradata stored procedure containing one or more parameters/variables that are a NUMBER data type. A NUMBER data type may be used to define IN, OUT, or INOUT parameters. A NUMBER data type may also be used to define local variables in the body of the stored procedure.

User-Defined Functions

You can invoke a UDF from a stored procedure control statement, as well as from SQL statements in a stored procedure that are not control statements. This includes UDFs which have VARIANT_TYPE input parameters.

Delimiters

All ANSI- and Teradata Database-supported delimiters can be used in stored procedures. Some examples are:
Use this delimiter … Named … To …
; semicolon end each statement in a stored procedure body, including DML, DDL, DCL statement, control statements, and control declarations.

The SEMICOLON character is the mandatory statement separator.

: colon prefix status variables and for-loop correlation names used in SQL statements other than control statements within stored procedures.

A COLON character must suffix the beginning label if used with a compound statement or iteration statement.

( left parenthesis enclose lists of parameters or CALL arguments.
) right parenthesis

Other delimiters like the comma character (,), the full stop character (.), and SQL operators in stored procedures is identical to their use elsewhere in Teradata SQL.

Lexical Separators

All lexical separators (comments, pad characters, and newline characters) supported by Teradata SQL can be used in stored procedures.

Newline characters need to be used wherever possible in the stored procedure body to increase its readability.

The newline character is implementation-specific and is typed by pressing the Enter key on non-3270 terminals or the Return key on 3270 terminals.

Locking Modifiers

Locking modifiers are supported with all DML, DDL, and DCL statements used in stored procedures except CALL.

Result Code Variables

For the definition and details of result code variables, see Result Code Variables, SQL Communications Area (SQLCA) and SQLSTATE Mappings.

For a complete listing of the Teradata Database return codes mapped to their corresponding SQLSTATE codes, see SQLSTATE Mappings.

For information on mapping SQLCODEs to the Teradata Database error codes, see SQL Communications Area (SQLCA).

Triggers

Triggers can call stored procedures, though the following restrictions apply:

The following statements are not allowed inside a stored procedure called from a trigger:
  • DDL statements
  • DCL statements
  • BT (BEGIN TRANSACTION) … ET (END TRANSACTION)
  • COMMIT
  • Exception handling statements

INOUT and OUT parameters are not allowed in a stored procedure called from a trigger.

A row can be passed to a stored procedure, but a table cannot.

In the following valid example, a row is passed to the stored procedure named Sp1:

CREATE TRIGGER Trig1 AFTER INSERT ON Tab1
REFERENCING NEW AS NewRow
FOR EACH ROW
(CALL Sp1(NewRow.C1,NewRow.C2);)

In the following example, a table is passed to a the stored procedure named Sp1. This operation is not valid, and it returns an error to the requestor.

CREATE TRIGGER Trig1 AFTER INSERT ON Tab1
REFERENCING NEW_TABLE AS NewTable
FOR EACH STATEMENT
(CALL Sp1(NewTable.c1,NewTable.C2);)

Queue Tables

Stored procedures support queue tables.

Multistatement Requests

Stored procedures support multistatement requests.

Comments

Comments, with the exception of nested bracketed comments, can be used in a stored procedure.

The ANSI/ISO SQL:2011 definition of comments includes what are sometimes described as Teradata Database-style comments. The standard discriminates between the comment types as follows:
Comment Structure ANSI Name
-- Simple comment
/* … */ Bracketed comment

Bracketed comments are sometimes called Teradata-style comments, though they are also defined by the ANSI/ISO SQL:2011 standard.

Related Topics

For more information about:
  • Rules applying to naming a stored procedure, see “SQL Lexicon” in Teradata Vantage™ - SQL Fundamentals, B035-1141.
  • Keywords, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
  • Local variables, see DECLARE and Supported Data Types.
  • Stored procedure parameters, see Supported Data Types.
  • Host Variables and “USING Row Descriptor,” see Teradata Vantage™ - SQL External Routine Programming , B035-1147 for a description of the same concept as a parameter, but going by a different name.
  • Rules, details and examples of the use of parameters in stored procedures, see the description of the CALL statement in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 and the description of the CREATE PROCEDURE statement in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Details on data types and usage considerations, see “SQL Lexicon” in Teradata Vantage™ - SQL Fundamentals, B035-1141 and Teradata Vantage™ - Data Types and Literals, B035-1143.
  • Guidelines for manipulating LOBS in a stored procedure, see “CREATE PROCEDURE (Internal form)” in Teradata Vantage™ - SQL Fundamentals, B035-1141.
  • The JSON data type, see Teradata Vantage™ - JSON Data Type, B035-1150.
  • Correct operation of a UDT within a stored procedure, see Teradata Vantage™ - SQL Fundamentals, B035-1141 for more information about these statements.
  • Stored procedures support multistatement requests, see the topic “SQL Multistatement Support in Stored Procedures” in “CREATE PROCEDURE (Internal Form)” in Teradata Vantage™ - SQL Fundamentals, B035-1141.
  • Queue tables, see Teradata Vantage™ - SQL Fundamentals, B035-1141.