Restrictions on Stored Procedures | VantageCloud Lake - Stored Procedure Lexicon

Names

The names of stored procedures and 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:

Name	Where Name Must Be Unique
Correlation or column	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 where 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 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, and therefore do not interfere with other identifiers used for local variables and parameters.
Local variable	BEGIN END compound statement where declared.
Parameter	Stored procedure. For example, a parameter name cannot be repeated for a local variable in the same stored procedure.
Stored procedure	Database (because storage procedure falls in the name space of tables, macros, triggers, and views).

Keywords

Keywords in stored procedures are not case-sensitive. Uppercase and lowercase can typically 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.

See Keywords.

Literals

All Vantage-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 Vantage-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.

A local variable specified in an SQL statement other than a control statement 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 must 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 must 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 may be caused by reused local variables in nested compound statements.

Parameters

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

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

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

A parameter that is not prefixed with a colon character must not be the same as a column name.

An SQL statement that contains an identifier that is the same as an SQL parameter and column name is interpreted as a column name. To prevent this, 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 running 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 statement 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 optional for an iteration statement or compound statement.
An ending label 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 its associated iteration statement or BEGIN END compound statement.
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 or compound statement.

FOR-Loop Variables

A FOR-loop variable is typically 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 its associated FOR statement.
In the case of nested FOR statements, the FOR-loop variable associated with an outer FOR statement can be referenced in statements inside the inner FOR statements.

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. Aliases are also called 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.
A reference to a correlation name or column in the body of the FOR iteration statement must be qualified 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.

User-Defined Functions

You can invoke a UDF from a stored procedure control statement and 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-supported delimiters can be used in stored procedures. Examples are:

Delimiter	Delimiter Name	Purpose
;	Semicolon	End each statement in a stored procedure body, including DML, DDL, DCL statement, control statements, and control declarations. The SEMICOLON character is the required 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	Enclose lists of parameters or CALL arguments.

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 return codes mapped to their corresponding SQLSTATE codes, see SQLSTATE Mappings.

For information on mapping SQLCODEs to the system 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
BEGIN TRANSACTION or BT
END TRANSACTION or ET
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 invalid, and 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.

Multiple-Statement Requests

Stored procedures support multiple-statement 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 described as Teradata-style comments. The standard discriminates between the comment types as follows:

Comment Structure	ANSI Name
--	Simple comment
/* … */	Bracketed comment

Bracketed comments are called Teradata-style comments, but are defined by the ANSI/ISO SQL:2011 standard.

Related Information

Rules applying to naming a stored procedure, see Object Names.
Local variables, see DECLARE.
Host Variables 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 following:
Details on data types and usage considerations, see Data Types and Data Types and Literals.
More information about stored procedures, see Stored Procedures.
The JSON data type, see JSON Data Type.
Stored procedures support multiple-statement requests, which are described in Multiple-Statement Requests.
Queue tables, see Queue Tables.

Restrictions on Stored Procedures | VantageCloud Lake - Stored Procedure Lexicon - Teradata VantageCloud Lake

Lake - Working with SQL