Supported DDL Statements
You can use the following SQL DDL statements in a stored procedure:
- ALTER FUNCTION
- ALTER TABLE
- ALTER TRIGGER
- BEGIN LOGGING
- COLLECT STATISTICS (Optimizer Form)
- COMMENT
- CREATE CAST
- CREATE DATABASE
- CREATE ERROR TABLE
- CREATE HASH INDEX
- CREATE INDEX
- CREATE JOIN INDEX
- CREATE MACRO
- CREATE ORDERING
- CREATE PROFILE
- CREATE RECURSIVE VIEW
- CREATE ROLE
- CREATE TABLE
- CREATE TRANSFORM
- CREATE TRIGGER
- CREATE USER
- CREATE VIEW
- DELETE DATABASE
- DELETE USER
- DROP CAST
- DROP DATABASE
- DROP ERROR TABLE
- DROP HASH INDEX
- DROP INDEX
- DROP JOIN INDEX
- DROP MACRO
- DROP ORDERING
- DROP PROCEDURE
- DROP PROFILE
- DROP ROLE
- DROP STATISTICS (Optimizer Form)
- DROP TABLE
- DROP TRANSFORM
- DROP TRIGGER
- DROP USER
- DROP VIEW
- END LOGGING
- MODIFY DATABASE
- MODIFY PROFILE
- MODIFY USER
- RENAME MACRO
- RENAME PROCEDURE
- RENAME TABLE
- RENAME TRIGGER
- RENAME VIEW
- REPLACE CAST
- REPLACE FUNCTION
- REPLACE MACRO
- REPLACE ORDERING
- REPLACE TRANSFORM
- REPLACE TRIGGER
- REPLACE VIEW
- SET QUERY_BAND = … FOR TRANSACTION
Usage Notes
| Statement | Notes |
|---|---|
| COMMENT | You can use only DDL COMMENT statements in a stored procedure. You cannot specify DML COMMENT statements, which are restricted to embedded SQL applications, to fetch the comments for database objects, columns of a table, and parameters. |
| CREATE TABLE | All variations of CREATE TABLE statement are valid. |
| CREATE VOLATILE TABLE | If you include a CREATE VOLATILE TABLE statement in a stored procedure, the volatile table is created in your login database. If an object with the same name already exists in that database, the result is a runtime exception. DML statements within a stored procedure referencing the volatile table must either have the user's login database as the qualifier, or not have any qualifying database name. |
| CREATE DATABASE/ CREATE USER | A CREATE DATABASE or CREATE USER statement in a stored procedure must contain a FROM clause. The specified database is the immediate owner of the USER or DATABASE created. If the CREATE DATABASE or CREATE USER omits the FROM clause, a compilation error is reported during procedure creation: 5568 – “SQL statement is not supported within a stored procedure.” If CREATE USER/ DATABASE without a FROM clause is specified as a dynamic SQL statement within a stored procedure, the same error is reported as a runtime exception during stored procedure execution. |
| SET QUERY_BAND | A SET QUERY_BAND statement in a stored procedure must specify the FOR TRANSACTION clause. You cannot set the query band for a session within a stored procedure. You can pass the query band specification string of a SET QUERY_BAND statement into a stored procedure as an IN or INOUT argument. |
Unsupported DDL Statements
You cannot use the following SQL DDL statements in a stored procedure:
- ALTER METHOD
- ALTER PROCEDURE
- ALTER TYPE
- CREATE FUNCTION
- CREATE METHOD
- CREATE PROCEDURE
- CREATE TABLE (queue and trace table forms)
- CREATE TYPE (all forms)
- DATABASE
- DROP TYPE
- EXPLAIN
- HELP (all forms)
- REPLACE METHOD
- REPLACE PROCEDURE
- REPLACE TYPE
- SET QUERY_BAND = … FOR SESSION
- SET ROLE
- SET SESSION (all forms)
- SET TIME ZONE
- SHOW (all forms)
Transaction Mode Impact on DDL Statements
The behavior of DDL statements specified in stored procedures at runtime depends on the transaction mode of the Teradata session in which the procedure is created.
- A DDL statement specified within an explicit (user-defined) transaction in a stored procedure in Teradata transaction mode must be the last statement in that transaction. Otherwise, a runtime exception (SQLCODE: 3932, SQLSTATE: ‘T3932’) is raised.
- When you execute a stored procedure in ANSI transaction mode, each DDL statement specified in the procedure body must be followed by a COMMIT WORK statement. Otherwise, a runtime exception (SQLCODE: 3722, SQLSTATE: ‘T3722’) is raised.
Related Topics
For more information about:
- Supported DDL statements, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
- Privilege checking performed for a SET QUERY_BAND statement, see Checking Privileges for Stored Procedures.