15.00 - Dynamic SQL in Stored Procedures - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Programming Reference

Dynamic SQL in Stored Procedures

Stored procedures support of dynamic SQL statements is different from embedded SQL support.

Use the following statement to set up and invoke dynamic SQL in a stored procedure:

   CALL DBC.SysExecSQL(string_expression)

where string_expression is any valid string expression that builds an SQL statement.

The string expression consists of string literals, status variables, local variables, input (IN and INOUT) parameters, and for-loop aliases. Dynamic SQL statements are not validated at compile time.

The resulting SQL statement cannot have status variables, local variables, parameters, for-loop aliases, or a USING or EXPLAIN modifier.


The following example uses dynamic SQL within stored procedure source text:

   CREATE PROCEDURE new_sales_table( my_table VARCHAR(30),
                                     my_database VARCHAR(30))
     DECLARE sales_columns VARCHAR(128) 
       DEFAULT '(item INTEGER, price DECIMAL(8,2), sold INTEGER)';
     CALL DBC.SysExecSQL('CREATE TABLE ' || my_database ||
                   '.' || my_table || sales_columns);

A stored procedure can make any number of calls to SysExecSQL. The request text in the string expression can specify a multistatement request, but the call to SysExecSQL must be delimited by BEGIN REQUEST and END REQUEST keywords.

Because the request text of dynamic SQL statements can vary from execution to execution, dynamic SQL provides more usability and conciseness to the stored procedure definition.


Whether the creator, owner, or invoker of the stored procedure must have appropriate privileges on the objects that the stored procedure accesses depends on whether the CREATE PROCEDURE statement includes the SQL SECURITY clause and which option the SQL SECURITY clause specifies.

The following SQL statements cannot be specified as dynamic SQL in stored procedures:


Related Topics

For rules and usage examples of dynamic SQL statements in stored procedures, see SQL Stored Procedures and Embedded SQL.