Static and Dynamic SQL Characteristics: Examples and Differences
Learn about the characteristics of static and dynamic SQL in Teradata stored procedures. Discover how to use variables and parameters in SQL statements.
Characteristics of Static SQL
- The SQL statement code cannot be changed during the Stored Procedure execution.
- Variables and parameters can be used to replace literals 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.
- Variables and parameters can be used to replace anything in the SQL statement.
- Dynamic SQL Statements must be executed by invocation or the opening of a cursor.
- Fewer errors are detectable during compilation.
Depending on the requirements, static and dynamic SQL can be mixed in Teradata stored procedures.
Let's make things more transparent with the help of some examples.
Static SQL examples:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
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 can use variables and parameters and be executed once code execution reaches the statement.
Get the next issue by email.
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 ||'; ';Before execution, Dynamic SQL is assigned to a character variable.
Numerous dynamic SQL errors remain undetectable during compilation. To illustrate this, we can examine the following instance of dynamic SQL:
REPLACE PROCEDURE TEST_SP()BEGINDECLARE mysql VARCHAR(1000);SET mysql = 'SELECT COL FROM TESTTABLE';CALL DBC.SYSEXECSQL(mysql);END;CALL TEST_SP();The stored procedure above will compile successfully. However, it will generate a runtime error because the SQL statement executed is invalid, as it returns a result set that is not assigned to a cursor.
Let's modify the aforementioned stored procedure by implementing static SQL.
REPLACE PROCEDURE TEST_SP()BEGINDECLARE mysql VARCHAR(1000);SELECT COL FROM TESTTABLE;END;CALL TEST_SP();The displayed stored procedure will result in a compile error.
Using volatile tables in stored procedures can have a significant impact. During compilation, all referenced objects must be available, which includes ensuring that any volatile tables created by static SQL are also available. Failure to do so will result in compilation errors.
REPLACE PROCEDURE TEST_SP()BEGINCREATE VOLATILE TABLE VOLTEST(PK INTEGER) PRIMARY INDEX (PK);END;CALL TEST_SP();Using dynamic SQL to create a volatile table eliminates the need for compile-time checking and availability of the table during compilation.
REPLACE PROCEDURE TEST_SP()BEGINDECLARE mysql VARCHAR(1000);SET mysql = 'CREATE VOLATILE TABLE VOLTEST(PK INTEGER) PRIMARY INDEX (PK);’;CALL DBC.SYSEXECSQL(mysql);END;CALL TEST_SP();Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.