SSC-EWI-0023

Performance Review - A loop contains an insert, delete, or update statement.

The EWI is only generated when Javascript is the target language for Stored Procedures. This is a deprecated translation feature, as Snowflake Scripting is the recommended target language for Stored Procedures.

Some parts in the output code are omitted for clarity reasons.

Severity

Low

Description

This warning indicates a possible consideration that the user should have in terms of performance.

Example Code

Input Code:

IN -> Teradata_01.sql

-- Additional Params: -t javascript
REPLACE PROCEDURE Database1.Proc1()
BEGIN
    DECLARE lNumber INTEGER DEFAULT 1;
    FOR class1 AS class2 CURSOR FOR 
      SELECT COL0,
      TRIM(COL1) AS COL1ALIAS,
      TRIM(COL2),
      COL3
      FROM someDb.prefixCol
    DO
      INSERT INTO TempDB.Table1 (:lgNumber, :lNumber, (',' || :class1.ClassCD || '_Ind CHAR(1) NOT NULL'));
      SET lNumber = lNumber + 1;
    END FOR;
END;

Output Code:

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE Database1.Proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    var LNUMBER = 1;
    /*** SSC-EWI-0023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT ***/
    for(var CLASS2 = new CURSOR(`SELECT
   COL0,
   TRIM(COL1) AS COL1ALIAS,
   TRIM(COL2),
   COL3
FROM
   someDb.prefixCol`,[],false).OPEN();CLASS2.NEXT();) {
        let CLASS1 = CLASS2.CURRENT;
        EXEC(`INSERT INTO TempDB.Table1
VALUES (:lgNumber, :1, (',' || :
!!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE  VARIABLE class1.ClassCD MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
:2 || '_Ind CHAR(1) NOT NULL'))`,[LNUMBER,CLASS1.CLASSCD]);
        LNUMBER = LNUMBER + 1;
    }
    CLASS2.CLOSE();
$$;

Recommendations

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