DML Statements in Stored Procedures | Teradata Vantage - DML Statements in 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™

Supported DML Statements

You can use the following SQL DML statements in a stored procedure:
  • ABORT
  • BEGIN TRANSACTION
  • END TRANSACTION
  • CALL
  • CLOSE
  • COLLECT STATISTICS (QCD Form)
  • COMMIT
  • DECLARE CURSOR (selection form)
  • DELETE (all forms)
  • DROP STATISTICS (QCD Form)
  • FETCH
  • INSERT
  • MERGE
  • OPEN
  • ROLLBACK
  • SELECT (only in cursors)
  • SELECT AND CONSUME TOP 1 (only in positioned cursors)
  • SELECT INTO
  • SELECT AND CONSUME TOP 1 INTO
  • UPDATE, including searched, positioned, and upsert form

Unsupported DML Statements

You cannot use the following SQL DML statements in a stored procedure:
  • CHECKPOINT
  • COLLECT DEMOGRAPHICS

Restricting SQL Statement Execution

The SQL_data_access clause in the CREATE/REPLACE PROCEDURE statement indicates whether or not the stored procedure can issue any SQL statements and, if so, what type. The SQL_data_access clause includes the following options:
This option... Indicates that the stored procedure can execute...
CONTAINS SQL SQL control statements. The stored procedure cannot read or modify SQL data.
READS SQL DATA statements that read SQL data, such as a FETCH statement. The stored procedure cannot execute statements that modify SQL data.
MODIFIES SQL DATA all SQL statements that can be called from a stored procedure, such as UPDATE, INSERT, or DELETE statements. This is the default when the clause is not included in the CREATE/REPLACE PROCEDURE statement.
The system returns an exception as follows:
If this option... Attempts to... This message returns...
CONTAINS SQL read or modify SQL data or calls a procedure that attempts to read or modify SQL data ‘2F004’ reading SQL-data not permitted.
READS SQL DATA modify SQL data or calls a procedure that attempts to modify SQL data ‘2F002’ modifying SQL-data not permitted.

Related Information

  • DML statements, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • SQL_data_access clause, see CREATE PROCEDURE (SQL form)/REPLACE PROCEDURE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • SQL_data_access exceptions, see SQL Communications Area (SQLCA) for SQLCODE to SQLSTATE mappings.
  • Supported DML statements, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.