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.
- 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 Teradata Vantage™ - SQL Stored Procedures and Embedded SQL , B035-1148 .