Characteristics of Static SQL

The SQL statement code cannot be changed during the Stored Procedure execution

be changed during the Stored Procedure execution Variables and parameters can be used to replace literals in the SQL statement

in the SQL statement Static SQL statements are executed as soon as code execution reaches the statement

Many errors can be detected already during the compilation

Characteristics of Dynamic SQL

The SQL statement code can be altered during the Stored Procedure execution

be altered during the Stored Procedure execution Variables and parameters can be used to replace anything in the SQL statement

in the SQL statement Dynamic SQL Statements have to be executed by invocation or the opening of a cursor

Fewer errors are detectable during compilation

Static and dynamic SQL can be mixed up in Teradata stored procedures, depending on the requirements.

Let’s make things more transparent with the help of some examples.

Static SQL examples:

UPDATE TESTTABLE SET COL = 1 WHERE COL = 2;

UPDATE TESTTABLE SET COL = 1 WHERE COL = :n ; — n is a declared variable

UPDATE TESTTABLE SET COL = 1 WHERE COL = n ; — n is a declared variable

Static SQL is put directly into the stored procedure. It is allowed to use variables and parameters and will be executed as soon as code execution reaches the statement.

Dynamic SQL examples:

SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE PK = ?’;

SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE COL = ‘ || n ||’; ‘;

SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE COL = ‘ || :n ||’; ‘;

Dynamic SQL is always assigned to a character variable before being executed.

As a result, many dynamic SQL errors are not detectable during compile time. Let’s analyze this by looking at the dynamic SQL example below:

REPLACE PROCEDURE TEST_SP()

BEGIN

DECLARE mysql VARCHAR(1000);

SET mysql = ‘SELECT COL FROM TESTTABLE’;

CALL DBC.SYSEXECSQL(mysql);

END;

CALL TEST_SP();

The above-stored procedure will compile without any problems, but it will create a runtime error as the executed SQL statement is not valid (it returns a result set that is not assigned to a cursor).

Now let’s change the stored procedure from above and use static SQL:

REPLACE PROCEDURE TEST_SP()

BEGIN

DECLARE mysql VARCHAR(1000);

SELECT COL FROM TESTTABLE;

END;

CALL TEST_SP();

The above shown stored procedure will cause a compile-time error.

There is another critical impact when using volatile tables in a stored procedure.

During compile-time, the availability of all referenced objects is checked. This means you have to make volatile tables created by static SQL have to be available during compile time. Otherwise, the compilation will fail:

REPLACE PROCEDURE TEST_SP()

BEGIN

CREATE VOLATILE TABLE VOLTEST

(

PK INTEGER

) PRIMARY INDEX (PK);

END;

CALL TEST_SP();

By wrapping the creation of a volatile table into dynamic SQL, no compile-time check can take place and therefore, there is no need to have the volatile tables available at compile time:

REPLACE PROCEDURE TEST_SP()

BEGIN

DECLARE mysql VARCHAR(1000);

SET mysql = ‘CREATE VOLATILE TABLE VOLTEST

(

PK INTEGER

) PRIMARY INDEX (PK);’;

CALL DBC.SYSEXECSQL(mysql);

END;

CALL TEST_SP();