DWHPro Insights

Static and Dynamic SQL Characteristics: Examples and Differences

By Roland Wenzlofsky ·

Static and Dynamic SQL Characteristics: Examples and Differences

 

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.
  • Dynamic SQL text can be constructed at runtime, but parameter markers bind data values, not arbitrary identifiers or SQL syntax. Validate and quote any identifiers assembled into statement text.
  • 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:

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.

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()
BEGIN
DECLARE 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()
BEGIN
DECLARE mysql VARCHAR(1000);
SELECT COL FROM TESTTABLE;
END;
CALL TEST_SP();

The displayed stored procedure will result in a compile error.

A static CREATE VOLATILE TABLE does not require the future table to exist at procedure compilation; that example compiled and ran in a fresh test session. Static statements referring to other objects have their own compile-time requirements, so distinguish table creation from later references.

REPLACE PROCEDURE TEST_SP()
BEGIN
CREATE VOLATILE TABLE VOLTEST
(
PK INTEGER
) PRIMARY INDEX (PK);
END;
CALL TEST_SP();

Dynamic CREATE also compiled and ran after correcting the quote. It defers checking of the dynamic statement to execution, but is not required solely because the created table does not yet exist.

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();

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Static and Dynamic SQL Characteristics: Examples and Differences”

  1. Hi Ronald
    I tried this but it doesn’t work, error msg “Text not terminated before END”
    –#######################################################################################
    –##
    –## REMOVE ALL DB ACCESS FROM A ROLE
    –## ————————————————————————————-
    –## ONLY WORKS WITH SANDBOX Databases for SECURITY
    –##
    –## A Ekladios
    –##
    –## 20/01/2016
    –## FINAL
    –##
    –######################################################################################

    REPLACE PROCEDURE EKLADIOSA.REMOVE_DB_ACCESS_FROM_ROLE_TST2 ( IN RL_NAME VARCHAR(30) , IN DB_NAME VARCHAR (30) )

    DYNAMIC RESULT SETS 3

    –##################################
    –## DECLARE THE VARIABLES USED
    –##################################

    BEGIN

    DECLARE ROLEEXISTS INTEGER ;
    DECLARE DBEXISTS INTEGER ;
    DECLARE MYSQL VARCHAR (200) ;
    DECLARE GR1 VARCHAR (200) ;
    DECLARE MSG VARCHAR (400) ;

    –####################################
    –## CHECK OBJECTS EXISTS MSG
    –####################################
    –####################################

    DECLARE RESULTS CURSOR WITH RETURN ONLY TO CLIENT FOR SELECT ‘ THIS ROLE ‘||RL_NAME|| ‘ DOES NOT EXIST, Please CHHECK NAME & TRY AGAIN ‘ ;

    DECLARE RESULTS2 CURSOR WITH RETURN ONLY TO CLIENT FOR SELECT ‘ ROLE ‘ || RL_NAME || ‘ DOES NOT HAVE ACCESS TO DATABASE ‘||DB_NAME|| ‘ , Please CHECK USER NAME & TRY AGAIN ‘ ;

    DECLARE RESULTS3 CURSOR WITH RETURN ONLY FOR MY_STATEMENT ;

    –#########################################################################
    –#########################################################################
    –## CHECK IF ROLE EXISTS
    –## THEN DISPLAY MSG AND EXIT THE PROCEDURE
    –#########################################################################
    –#########################################################################

    LABEL_ROLE : BEGIN

    SELECT COUNT(*) INTO ROLEEXISTS FROM DBC.RoleInfo WHERE TRIM (RoleName) = RL_NAME ;

    IF ROLEEXISTS = 0 THEN

    OPEN RESULTS ;

    LEAVE LABEL_ROLE ;

    ELSE

    –#########################################################################
    –#########################################################################
    –## CHECK IF DB A EXISTS
    –## THEN DISPLAY MSG AND EXIT THE PROCEDURE
    –#########################################################################
    –#########################################################################
    LABEL_ACCESS: BEGIN

    SELECT COUNT (*) INTO DBEXISTS
    FROM DBC.AllRoleRights
    WHERE DatabaseName = DB_NAME
    AND RoleName = RL_NAME ;

    IF DBEXISTS = 0 THEN
    OPEN RESULTS2 ;
    LEAVE LABEL_ACCESS ;

    ELSE
    –#########################################################################
    –#########################################################################
    –## REMOVE ALL DB Access from ROLE
    –#########################################################################
    –#########################################################################

    SET GR1 = ‘REVOKE ALL ON ‘ || DB_NAME|| ‘ FROM ‘ || RL_Name || ‘ ‘ ;
    EXECUTE IMMEDIATE GR1;
    END IF ;

    END LABEL_ACCESS;

    END IF ;
    END LABEL_ROLE ;

    –#########################################################################
    –#########################################################################
    –## VERIFY RESULTS
    –#########################################################################
    –#########################################################################

    SET MYSQL=’ SELECT ‘ || ”” || ‘DB DOES NOT EXIST IN ROLE ‘ || ”” ||
    ‘ WHERE NOT EXISTS
    ( SELECT DatabaseName FROM DBC.AllRoleRights WHERE RoleName = ‘ ||””|| RL_NAME || ”” || ‘ AND DatabaseName = ‘ || ””||DB_NAME||”” || ‘) ‘
    || ‘ UNION ‘ ||
    ‘ SELECT ‘ || ”” || ‘DB DOES EXIST IN ROLE ‘ || ”” ||
    ‘ WHERE EXISTS ( SELECT DatabaseName FROM DBC.AllRoleRights WHERE RoleName = ‘ ||””||RL_NAME|| ”” || ‘ AND DatabaseName = ‘ || ””|| DB_NAME ||”” || ‘) ; ‘ ; CALL DBC.SYSEXECSQL (MYSQL) ;

    — PREPARE MY_STATEMENT FROM MYSQL;

    — OPEN RESULTS3 ;

    –##############################################################################################################################
    –##
    –## USAGE & How to run
    –##
    –## CALL USERS.REMOVE_DB_ACCESS_FROM_ROLE ( ‘Role_Name’ , ‘Database_Name’ )
    –##
    –###############################################################################################################################

    END;

    If I comment out last setion “SET MYSQL then it works fine but this is not what I want. Any idea?
    I am trying to verify the removal of the database from the role.
    Thanks

    Reply

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam