SnowConvert AI - Teradata - SQL en JavaScript (Procédures)¶

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

    //------ Local variables --------
    var SQL_CMD = ` `;
    var NUM_COLS;
    var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
    //----- Declare cursor -------
    var CUR2 = new CURSOR(`SELECT COUNT(columnname) FROM table1`,[],false);
    //------ Set --------
    SQL_CMD = `SELECT * from table1`;
    //------ Prepare cursor --------
    var FIRSTSTATEMENT = SQL_CMD;
    //------ Open cursors --------
    RESULTSET.OPEN();
    CUR1.OPEN();
    //------ Fetch -------------
    CUR1.FETCH() && ([val1,val2] = CUR1.INTO());
    //------ Close cursor --------
    CUR1.CLOSE();
    return PROCRESULTS();
$$;

 while ( counter < 10) {
    counter = counter + 1;
}

 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 AI 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();
$$;

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

    var VAR1;
    var VAR2;
    var WEEKSTART;
    WEEKSTART = `2019-03-03`;
    VAR1 = `something`;
    VAR2 = 123;
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`SELECT * FROM TABLE1 WHERE SOMETHING = :1`,[PARAM1]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`SELECT * FROM TABLE1 WHERE :1 = :1 AND date1 = :2 AND param2 = :3`,[VAR1,WEEKSTART,PARAM2]);
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`INSERT INTO TABLE2 (col1, col2, col3, col4, col5) VALUES (:1, :2, :3, :4, :5)`,[PARAM1,PARAM2,VAR1,VAR2,WEEKSTART]);
$$;

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

 var V_PROPVAL;
 var CUR1 = new CURSOR(`SELECT propID from viewName.viewCol
  where
  propval is not null`,[],false);
  LABEL_WHILE: {
  while ( SQLCODE == 0 ) {
   if (SQLSTATE == `02000`) {
    break LABEL_WHILE;
   }
   LABEL_INNER_WHILE: {
    while ( SQLCODE == 0 ) {
     if (SQLSTATE == `02000`) {
      break LABEL_INNER_WHILE;
     }
    }
   }
   EXEC(`SELECT * FROM TABLE1`,[]);
  }
 }
$$;

-- Additional Params: -t JavaScript
REPLACE PROCEDURE Procedure1(OUT P1 INTEGER)               
    DYNAMIC RESULT SETS 2
    BEGIN
        DECLARE SQL_CMD,SQL_CMD_1  VARCHAR(20000) DEFAULT ' ';
        DECLARE RESULTSET CURSOR WITH RETURN ONLY FOR FIRSTSTATEMENT;
        SET SQL_CMD = 'SEL * FROM EMPLOYEE';
        PREPARE FIRSTSTATEMENT FROM SQL_CMD; 
        OPEN RESULTSET;
        SET P1 = (SEL CAST(AVG(AGE) AS INTEGER) FROM EMPLOYEE);
    END;

 -- Additional Params: -t JavaScript
CREATE PROCEDURE Procedure2()
BEGIN
    DECLARE x INTEGER;
    CALL Procedure1(x);
END;