16.10 - Dynamic SQL in Stored Procedures - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

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.

Example: Using Dynamic SQL Within Stored Procedure Source Text

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

   CREATE PROCEDURE new_sales_table( my_table VARCHAR(30),
                                     my_database VARCHAR(30))
   BEGIN
     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);
   END;

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.

Restrictions

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:

  • ALTER PROCEDURE
  • CALL
  • CREATE PROCEDURE
  • DATABASE
  • EXPLAIN modifier
  • HELP
  • OPEN
  • PREPARE
  • REPLACE PROCEDURE
  • SELECT
  • SET ROLE
  • SET SESSION ACCOUNT
  • SET SESSION COLLATION
  • SET SESSION DATEFORM
  • SET TIME ZONE
  • SHOW
  • Cursor statements, including:
  • CLOSE
  • FETCH
  • OPEN

Related Topics

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