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 up 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 ||’; ‘;
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. Still, it will create a runtime error as the executed SQL statement is invalid (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();
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