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

Dynamic SQL is a method of invoking an SQL statement by compiling and performing it at runtime from within a stored procedure. You can invoke DDL, DML or DCL statements, with some exceptions, as dynamic SQL in a stored procedure.

Dynamic SQL Statements

Dynamic SQL statements are those statements whose request text can vary from execution to execution. They provide more usability and conciseness to the stored procedure definition.

Invoking Dynamic SQL that Does Not Return a Results Set

Example: Using Dynamic SQL Statements With an EXECUTE IMMEDIATE Statement

The following example illustrates the use of dynamic SQL statements within a stored procedure that does not return a results set and that uses an EXECUTE IMMEDIATE statement.

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)' ;
DECLARE sqlstr VARCHAR(500);
SET sqlstr = 'CREATE TABLE ' || my_database || 
                '.' || my_table || sales_columns ;
EXECUTE IMMEDIATE sqlstr;
END;

Example: Using Dynamic SQL Statements With a CALL Statement

The following example illustrates the use of dynamic SQL statements within a stored procedure that does not return a results set and that uses a CALL statement:

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;

Invoking Dynamic SQL that Returns a Results Set

You can invoke dynamic SQL in a stored procedure that returns a results set using the dynamic form of the DECLARE CURSOR (Stored Procedures Form) statement.

Example: Using Dynamic SQL Statements Within a Stored Procedure that Returns a Results Set

The following example illustrates the use of dynamic SQL statements within a stored procedure that returns a results set. Note that the procedure does not use a WITH RETURN statement.

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
  DECLARE SqlStr VARCHAR(1000);
  DECLARE C1 CURSOR FOR S1;
  SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
  PREPARE S1 FROM SqlStr;
  OPEN C1 USING EmpName;
  FETCH C1 INTO Salary;
  CLOSE C1;
END;

SQL Statements that Cannot Be Used Dynamically

Ownership of Objects Created or Referenced Within Dynamic SQL Statements

The rules for objects referenced in, or created through the dynamic SQL statements in a stored procedure are identical to the rules for objects referenced in other statements.

Rules for Dynamic SQL Statements

Dynamic SQL statements are not validated at compile time, that is, during stored procedure creation. The validation is done only during execution of the stored procedure.

You can specify multistatement requests in dynamic SQL requests within a BEGIN REQUEST … END REQUEST block. Otherwise, the error 5568 (SQL statement is not supported within a stored procedure) is reported during stored procedure execution.

The ending semicolon character is optional in the dynamically built SQL statement.

You can use only a DDL COMMENT statement as dynamic SQL in a stored procedure. You cannot specify a DML COMMENT statement to fetch the comments for database objects, columns of a table, and parameters.

A CREATE DATABASE or CREATE USER statement used as dynamic SQL in a stored procedure must contain the FROM clause.

The CALL DBC.SysExecSQL statement can be used any number of times in a stored procedure. With each call, only a single SQL statement can be specified in the string expression for dynamic SQL.

The size of each dynamic SQL request (the string_expression) cannot exceed 32000 characters.

No specific privilege is required to use the CALL DBC.SysExecSQL statement.

Related Topics

For more information about objects referenced in, or created through the dynamic SQL statements, see Ownership of Objects Created by Stored Procedures.