Restrictions on Stored Procedures | Teradata Vantage - Restrictions on Stored Procedures - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
vqj1592443206677.ditamap
dita:ditavalPath
vqj1592443206677.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

The stored procedure body size of a stored procedure is limited to 6.4 MB. But there is no limit on the stored procedure object code (compiled stored procedure) size.

The parser limits apply if the SQL statements within a stored procedure are large or complex.

The number of nested CALL statements, including recursion, cannot exceed 15.

The number of parameters in a procedure cannot exceed 256.

Stored procedures cannot be renamed across databases.

A stored procedure created in ANSI transaction mode cannot be executed in Teradata transaction mode, and vice versa. You can, however, execute the stored procedure after recreating it in the new session mode using REPLACE PROCEDURE.

A stored procedure created on one platform cannot be executed on another platform. But this limitation can be overcome by recompiling a stored procedure using the ALTER PROCEDURE statement.

If a stored procedure is the only statement in a macro, you can execute a procedure from the macro.

Stored procedures do not support the following:
  • EXPLAIN and USING request modifiers within a stored procedure
  • EXECUTE macro statement
  • WITH clause within a stored procedure.
Stored procedures, as well as macros, do not support the following query logging statements:
  • BEGIN QUERY LOGGING
  • END QUERY LOGGING
  • FLUSH QUERY LOGGING
  • REPLACE QUERY LOGGING

A stored procedure created in a particular date form always displays the same date-time format without respect to the date form set for the executing session.

The queue table form of CREATE TABLE cannot be executed in a stored procedure. All other forms of the CREATE TABLE statement are valid.

DDL and DCL statements for Row Level Security (RLS) administration is not be allowed to be defined in a stored procedure. RLS provides the capability to control data access based on security policies, data sensitivity classifications, and security credentials assigned to users. Supported RLS constraint checks will be executed if a user does not have override privileges.

When you use CURRENT_TIME or CURRENT_TIMESTAMP in a stored procedure and you have manually changed DBS Control General Fields 16 (System TimeZone Hour) and 17 (System TimeZone Minute), the stored procedure must be recompiled. It is not necessary to set the TimeZoneString in tdlocaledef to change the time zone automatically.

Related Information

  • REPLACE PROCEDURE or ALTER PROCEDURE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • CREATE TABLE (Queue Table Form), see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146