SSC-EWI-0030

The statement below has usages of dynamic SQL

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

Input

IN -> Teradata_01.sql

REPLACE PROCEDURE teradata_dynamic_sql()
BEGIN
  DECLARE str_sql VARCHAR(20);
  SET str_sql = 'UPDATE TABLE
                    SET COLA = 0,
                        COLB = ''test''';

  EXECUTE IMMEDIATE str_sql;
  EXECUTE IMMEDIATE 'INSERT INTO TABLE1(COL1) VALUES(1)';
  EXECUTE str_sql;
  CALL DBC.SysExecSQL('INSERT INTO TABLE1(COL1) VALUES(1)');
END;

Output

Oracle

Input

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE oracle_dynamic_sql
AS
    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';

    EXECUTE IMMEDIATE dynamic_statement;
    EXECUTE IMMEDIATE '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;

Output

SQL Server

Input

IN -> SqlServer_01.sql

CREATE OR ALTER PROCEDURE transact_dynamic_sql
AS
BEGIN
    DECLARE @dynamicStatement AS VARCHAR(200);
    DECLARE @numericVariable AS VARCHAR(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;

Output

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:

Oracle

IN -> Oracle_02.sql

SELECT dbms_random.value() FROM dual;

CREATE OR REPLACE PROCEDURE dynamic_sql_procedure
AS
  result VARCHAR(100) := 'SELECT dbms_random.value() from dual';
BEGIN
  NULL;  
END;

Snowflake

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.

• If you need more support, you can email us at [email protected]