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

CREATE OR REPLACE PROCEDURE teradata_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    LET str_sql VARCHAR(20);
    str_sql := 'UPDATE "TABLE"
   SET COLA = 0,
       COLB = ''test''';
    --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE str_sql;
    --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE 'INSERT INTO TABLE1 (COL1)
VALUES (1);';
    --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE str_sql;
    --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE 'INSERT INTO TABLE1 (COL1)
VALUES (1);';
  END;
$$;

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

CREATE OR REPLACE PROCEDURE oracle_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        dynamic_statement VARCHAR(100);
        numeric_variable INTEGER;
        dynamic_statement VARCHAR(100);
        column_variable VARCHAR(100);
        cursor_variable_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
        c INTEGER;
        dynamic_statement VARCHAR(100);
    BEGIN
        dynamic_statement := 'INSERT INTO sample_table(col1) VALUES(1)';
        numeric_variable := 3;
        column_variable := 'col1';
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        EXECUTE IMMEDIATE :dynamic_statement;
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
    EXECUTE IMMEDIATE 'INSERT INTO sample_table(col1) VALUES(' || NVL(:numeric_variable :: STRING, '') || ')';
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        cursor_variable_res := (
            EXECUTE IMMEDIATE :dynamic_statement
        );
        LET cursor_variable CURSOR
        FOR
            cursor_variable_res;
        OPEN cursor_variable;
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        cursor_variable_res := (
            EXECUTE IMMEDIATE 'SELECT ' || NVL(:column_variable :: STRING, '') || ' FROM
   sample_table'
        );
        --** MSC-ERROR - MSCEWI3133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR **
        LET cursor_variable CURSOR
        FOR
            cursor_variable_res;
        OPEN cursor_variable;
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        cursor_variable_res := (
            EXECUTE IMMEDIATE 'SELECT col1 FROM
   sample_table'
        );
        --** MSC-ERROR - MSCEWI3133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR **
        LET cursor_variable CURSOR
        FOR
            cursor_variable_res;
        OPEN cursor_variable;
        c :=
--             --** MSC-ERROR - MSCEWI3076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.OPEN_CURSOR' IS NOT CURRENTLY SUPPORTED. **
--             DBMS_SQL.OPEN_CURSOR
                                 ;
        dynamic_statement := 'SELECT * FROM
   sample_table';
--        --** MSC-ERROR - MSCEWI3076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.PARSE' IS NOT CURRENTLY SUPPORTED. **
--        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
--    DBMS_SQL.PARSE(:c, :dynamic_statement)
                                          ;
    END;
$$;

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

--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "sampleTable" **
CREATE OR REPLACE PROCEDURE transact_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        DYNAMICSTATEMENT VARCHAR(200);
        NUMERICVARIABLE VARCHAR(200);
    BEGIN
         
         
        DYNAMICSTATEMENT := 'INSERT INTO sample_table (col1) VALUES(1);';
        NUMERICVARIABLE := '3';
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        EXECUTE IMMEDIATE :DYNAMICSTATEMENT;
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        EXECUTE IMMEDIATE 'INSERT INTO sampleTable (col1) VALUES (' || :NUMERICVARIABLE || ');';
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        --** MSC-ERROR - MSCEWI1021 - EXECUTE AS USER/LOGIN NOT SUPPORTED **
        EXECUTE IMMEDIATE 'INSERT INTO sampleTable (col1) VALUES(10);';
--        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
--        --** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INSERT WITH EXECUTE IS PLANNED TO BE DELIVERED IN THE FUTURE **
--        INSERT INTO sampleTable EXECUTE sp_executesql STATEMENT = 'SELECT * FROM sampleTable;'
                                                                                              ;
--        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
--        --** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INSERT WITH EXECUTE IS PLANNED TO BE DELIVERED IN THE FUTURE **
--    INSERT INTO sampleTable EXECUTE IMMEDIATE '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:

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

SELECT
  --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_RANDOM.VALUE' INSERTED. **
  --** MSC-WARNING - MSCEWI3112 - DBMS_RANDOM.VALUE DIGITS OF PRECISION IS LOWER IN SNOWFLAKE **
  DBMS_RANDOM.VALUE() FROM dual;

CREATE OR REPLACE PROCEDURE dynamic_sql_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    result VARCHAR(100) := 'SELECT
   DBMS_RANDOM.VALUE() from dual';
  BEGIN
    NULL;
  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.

• If you need more support, you can email us at [e-mail]