Some parts in the output code are omitted for clarity reasons.
Severity
Medium
Description
This error is used to indicate that the statement has usages of dynamic SQL. Each specific source language has its own set of statements that can execute dynamic SQL. Dynamic SQL refers to code that is built as text using the string manipulation tools the database engine language provides.
This scenario is considered a complex pattern because dynamic SQL is built and executed in runtime making it more difficult to track and debug errors. This error is meant to be a helper to spot some problems that a static-code analyzer such as Snow Convert cannot.
Code Example
Teradata
IN -> Teradata_01.sql
REPLACEPROCEDURE teradata_dynamic_sql()BEGINDECLARE str_sql VARCHAR(20);SET str_sql ='UPDATE TABLE SET COLA = 0, COLB = ''test''';EXECUTEIMMEDIATE str_sql;EXECUTEIMMEDIATE'INSERT INTO TABLE1(COL1) VALUES(1)';EXECUTE str_sql;CALL DBC.SysExecSQL('INSERT INTO TABLE1(COL1) VALUES(1)');END;
OUT -> Teradata_01.sql
CREATEORREPLACEPROCEDURE teradata_dynamic_sql ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTEASCALLERAS$$BEGIN LET str_sql VARCHAR(20); str_sql :='UPDATE "TABLE" SET COLA = 0, COLB = ''test'''; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE str_sql; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE'INSERT INTO TABLE1 (COL1)VALUES (1);'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE str_sql; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE'INSERT INTO TABLE1 (COL1)VALUES (1);';END;$$;
Oracle
IN -> Oracle_01.sql
CREATEORREPLACEPROCEDURE oracle_dynamic_sqlAS dynamic_statement VARCHAR(100); numeric_variable INTEGER; dynamic_statement VARCHAR(100); column_variable VARCHAR(100); cursor_variable SYS_REFCURSOR; c INTEGER; dynamic_statement VARCHAR(100);BEGIN dynamic_statement :='INSERT INTO sample_table(col1) VALUES(1)'; numeric_variable :=3; column_variable :='col1';EXECUTEIMMEDIATE dynamic_statement;EXECUTEIMMEDIATE'INSERT INTO sample_table(col1) VALUES('|| numeric_variable ||')';OPEN cursor_variable FOR dynamic_statement;OPEN cursor_variable FOR'SELECT '|| column_variable ||' FROM sample_table';OPEN cursor_variable FOR'SELECT col1 FROM sample_table'; c := DBMS_SQL.OPEN_CURSOR; dynamic_statement :='SELECT * FROM sample_table'; DBMS_SQL.PARSE(c, dynamic_statement);END;
OUT -> Oracle_01.sql
CREATEORREPLACEPROCEDURE oracle_dynamic_sql ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$DECLARE dynamic_statement VARCHAR(100); numeric_variable INTEGER; dynamic_statement VARCHAR(100); column_variable VARCHAR(100); cursor_variable_res RESULTSET; c INTEGER; dynamic_statement VARCHAR(100);BEGIN dynamic_statement :='INSERT INTO sample_table(col1) VALUES(1)'; numeric_variable :=3; column_variable :='col1';EXECUTEIMMEDIATE :dynamic_statement; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE'INSERT INTO sample_table(col1) VALUES('|| NVL(:numeric_variable :: STRING, '') ||')'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! cursor_variable_res := (EXECUTEIMMEDIATE :dynamic_statement ); LET cursor_variable CURSORFOR cursor_variable_res;OPEN cursor_variable; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! cursor_variable_res := (EXECUTEIMMEDIATE'SELECT '|| NVL(:column_variable :: STRING, '') ||' FROM sample_table' ); !!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
LET cursor_variable CURSORFOR cursor_variable_res;OPEN cursor_variable; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! cursor_variable_res := (EXECUTEIMMEDIATE'SELECT col1 FROM sample_table' ); !!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
LET cursor_variable CURSORFOR cursor_variable_res;OPEN cursor_variable; c := !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.OPEN_CURSOR' IS NOT CURRENTLY SUPPORTED. ***/!!!
''AS OPEN_CURSOR; dynamic_statement :='SELECT * FROM sample_table'; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.PARSE' IS NOT CURRENTLY SUPPORTED. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
DBMS_SQL.PARSE(:c, :dynamic_statement);END;$$;
SQL Server
IN -> SqlServer_01.sql
CREATEORALTERPROCEDURE transact_dynamic_sqlASBEGINDECLARE @dynamicStatement ASVARCHAR(200);DECLARE @numericVariable ASVARCHAR(200);SET @dynamicStatement ='INSERT INTO sample_table(col1) VALUES(1);';SET @numericVariable ='3';EXECUTE (@dynamicStatement);EXEC ('INSERT INTO sampleTable(col1) VALUES ('+ @numericVariable +');');EXECUTE ('INSERT INTO sampleTable(col1) VALUES(10);') AS USER ='DbAdmin';INSERT INTO sampleTable EXECUTE sp_executesql @statement ='SELECT * FROM sampleTable;';INSERT INTO sampleTable EXECUTE ('SELECT * FROM sampleTable;');END;
OUT -> SqlServer_01.sql
CREATEORREPLACEPROCEDURE transact_dynamic_sql ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTEASCALLERAS$$DECLARE DYNAMICSTATEMENT VARCHAR(200); NUMERICVARIABLE VARCHAR(200);BEGIN DYNAMICSTATEMENT :='INSERT INTO sample_table (col1) VALUES(1);'; NUMERICVARIABLE :='3'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE :DYNAMICSTATEMENT; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTEIMMEDIATE'INSERT INTO sampleTable (col1) VALUES ('|| :NUMERICVARIABLE ||');'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - EXECUTE AS USER/LOGIN NOT SUPPORTED IN SNOWFLAKE ***/!!!
EXECUTEIMMEDIATE'INSERT INTO sampleTable (col1) VALUES(10);'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INSERT WITH EXECUTE' NODE ***/!!!
INSERT INTO sampleTable !!!RESOLVE EWI!!! /*** SSC-EWI-TS0075 - TRANSLATION FOR BUILT-IN PROCEDURE 'sp_executesql' IS NOT CURRENTLY SUPPORTED. ***/!!!
EXECUTE sp_executesql STATEMENT='SELECT * FROM sampleTable;'; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INSERT WITH EXECUTE' NODE ***/!!!
INSERT INTO sampleTable EXECUTEIMMEDIATE'SELECT *FROM sampleTable;';END;$$;
Issues Inside of Dynamic SQL
Something important to take into account is that when migrating dynamic SQL code, SnowConvert will not report any type of issue inside of dynamic SQL in the output code or in the assessment reports. This will happen even when the documentation of an issue or the translation specification describes that an issue will always be added to the output code. Here is an example of a migration in Oracle where this situation might be encountered:
IN -> Oracle_02.sql
SELECTdbms_random.value() FROM dual;CREATE OR REPLACE PROCEDURE dynamic_sql_procedureAS result VARCHAR(100) :='SELECT dbms_random.value() from dual';BEGINNULL; END;
OUT -> Oracle_02.sql
SELECT--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **DBMS_RANDOM.VALUE_UDF() FROM dual;CREATE OR REPLACE PROCEDURE dynamic_sql_procedure ()RETURNS VARCHARLANGUAGE SQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLERAS$$DECLARE result VARCHAR(100) :='SELECT DBMS_RANDOM.VALUE_UDF() from dual';BEGINNULL;END;$$;
In the previous example, the query and the variable assignment inside the procedure will be converted exactly the same, the difference is that in the dynamic SQL code the conversion issues will not be shown in the output code and in the assessment reports.
Recommendations
Use this tag to track every dynamically built statement and review its correctness when troubleshooting.