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 ||’; ‘;
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.
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()
BEGIN
CREATE 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()
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