Dynamic SQL in Stored Procedures | SQL Fundamentals | Teradata Vantage - 17.10 - Dynamic SQL in Stored Procedures - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
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 Information

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