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.