fbpx

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

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • 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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >