MSCCP0004

The statement below has usages of dynamic SQL

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

High

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

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

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

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

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]