Translation reference to convert Teradata PROCEDURE statement to Snowflake
Description
Teradata's PROCEDURE statement is translated to Snowflake PROCEDURE syntax.
For more information on Teradata PROCEDURE, click here.
Sample Source Patterns
Create Procedure Transformation
SnowConvert Helpers Code was removed from the example. You can find them here.
SnowConvert procedure helpers region
All procedures contain a region called SnowConvert Helpers code, these are a set of variables and functions that help to emulate some Teradata functionality that is not native in JavaScript. For simplicity in the procedures samples code this region will not be shown.
Some helpers are added always like EXEC and some others like FETCH, INTO, etc. are added on demand.
casevaluewhen0thenselect*from table1elseupdate table1 setname="SpecificValue"where id =value;endcase
Snowflake
switch(value) {case0:EXEC(`SELECT * FROM PUBLIC.table1`,[]);break;default:EXEC(`UPDATE PUBLIC.table1 set name = "SpecificValue" where id = value`,[]);break;}
CREATEPROCEDURE procedure1() DYNAMIC RESULT SETS2BEGIN-------- Local variables --------DECLARE sql_cmd VARCHAR(20000) DEFAULT' '; DECLARE num_cols INTEGER;------- Declare cursor with return only-------DECLARE resultset CURSORWITHRETURN ONLY FOR firststatement;------- Declare cursor -------DECLARE cur2 CURSORFORSELECTCOUNT(columnname) FROM table1;-------- Set --------SET sql_cmd='sel * from table1';-------- Prepare cursor -------- PREPARE firststatement FROM sql_cmd; -------- Open cursors --------OPEN resultset; OPEN cur1;-------- Fetch -------------FETCH cur1 INTO val1, val2;-------- Close cursor --------CLOSE cur1;END;
Snowflake
CREATEORREPLACEPROCEDURE PUBLIC.procedure1 ()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code//------ Local variables -------- var SQL_CMD =` `; var NUM_COLS; var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);//----- Declare cursor ------- var CUR2 = new CURSOR(`SELECT COUNT(columnname) FROM PUBLIC.table1`,[],false);//------ Set -------- SQL_CMD =`SELECT * FROM PUBLIC.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();$$;a
REPLACEPROCEDURE Database1.Proc1()BEGINDECLARE lNumber INTEGERDEFAULT1;FOR class1 AS class2 CURSORFORSELECT COL0,TRIM(COL1) AS COL1ALIAS,TRIM(COL2), COL3FROM someDb.prefixCol DOINSERT INTO TempDB.Table1 (:lgNumber, :lNumber, (','|| :class1.ClassCD ||'_Ind CHAR(1) NOT NULL'));SET lNumber = lNumber +1;ENDFOR;END;
Snowflake
CREATEORREPLACEPROCEDURE Database1.PUBLIC.Proc1 ()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var LNUMBER =1; //** MSC-WARNING - MSCEWI1023 - 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.PUBLIC.prefixCol`,[],false).OPEN();CLASS2.NEXT();) { let CLASS1 = CLASS2.CURRENT; EXEC(`INSERT INTO TempDB.PUBLIC.Table1 VALUES (:lgNumber, :1, (',' || :/*** MSC-WARNING - MSCEWI1026 - 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();$$;
Note: The FOR loop present in the Teradata procedure is transformed to a FOR block in javascript that emulates its functionality.
Procedure parameters and variables referenced inside statements
The transformation for the procedure parameters and variables that are referenced inside the statements of the procedure is:
Teradata
REPLACEPROCEDURE PROC1 (param1 INTEGER, param2 VARCHAR(30))BEGINDECLARE var1 VARCHAR(1024); DECLARE var2 SMALLINT;DECLARE weekstart date; set weekstart='2019-03-03';set var1 ='something';set var2 =123;SELECT*FROM TABLE1 WHERE SOMETHING = :param1;SELECT*FROM TABLE1 WHERE var1 = var1 AND date1 = weekstart AND param2 = :param2;INSERT INTO TABLE2 (col1, col2, col3, col4, col5) VALUES (:param1, :param2, var1, var2, weekstart);END;
Snowflake
CREATEORREPLACEPROCEDURE sfSchema.PROC1 (param1 FLOAT, param2 STRING)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var VAR1; var VAR2; var WEEKSTART; WEEKSTART =`2019-03-03`; VAR1 =`something`; VAR2 =123; // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`SELECT * FROM PUBLIC.TABLE1 WHERE SOMETHING = :1`,[PARAM1]); // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`SELECT * FROM PUBLIC.TABLE1 WHERE :1 = :2 AND date1 = :3 AND param2 = :4`,[VAR1,VAR1,WEEKSTART,PARAM2]); // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO PUBLIC.TABLE2 (col1, col2, col3, col4, col5) VALUES (:1, :2, :3, :4, :5)`,[PARAM1,PARAM2,VAR1,VAR2,WEEKSTART]);
$$;
Note: Whenever a procedure parameter or a variable declared inside the procedure is referenced inside a Teradata statement that has to be converted,this reference is escaped from the resulting text to preserve the original reference's functionality.
Leave
In Javascript, it's possible to use break with an additional parameter, thus emulating the behavior of a Teradata LEAVE jump.
Labels can also be emulated by using Javascript Labeled Statements.
REPLACEPROCEDURE PROC1 ()BEGINDECLARE v_propval VARCHAR(1024);DECLARE Cur1 cursorforSelect propIDfrom viewName.viewColwhere propval is not null;LABEL_WHILE:WHILE (SQLCODE =0) DOIF (SQLSTATE ='02000' ) THEN LEAVE LABEL_WHILE;ENDIF; LABEL_INNER_WHILE:WHILE (SQLCODE =0) DOIF (SQLSTATE ='02000' ) THEN LEAVE LABEL_INNER_WHILE;ENDIF;ENDWHILE LABEL_INNER_WHILE;SELECT*FROM TABLE1;ENDWHILE L1;END;
Snowflake
CREATEORREPLACEPROCEDURE PUBLIC.PROC1 ()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$ var v_propval; var CUR1 = new CURSOR(`SELECT propID FROM viewName.PUBLIC.viewCol where propval is not null`,[],false); L1: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 PUBLIC.TABLE1`,[]); } $$;
Getting Results from Procedures
Description of the translation
In Teradata, there are two ways to return data from a procedure. The first is through output parameters and the second through Dynamic Result Sets and Cursors. Both are shown in the following example. Each important point is explained below.
Example of returning data from a Stored Procedure
Teradata
REPLACEPROCEDURE Procedure1(OUT P1 INTEGER) DYNAMIC RESULT SETS2BEGINDECLARE SQL_CMD,SQL_CMD_1 VARCHAR(20000) DEFAULT' ';DECLARE RESULTSET CURSORWITHRETURN ONLY FOR FIRSTSTATEMENT;SET SQL_CMD ='SEL * FROM EMPLOYEE'; PREPARE FIRSTSTATEMENT FROM SQL_CMD; OPEN RESULTSET;SET P1 = (SEL CAST(AVG(AGE) ASINTEGER) FROM EMPLOYEE);END;
Snowflake
CREATEORREPLACEPROCEDURE PUBLIC.Procedure1 (P1 FLOAT)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var ..., _OUTQUERIES = [], DYNAMIC_RESULTS =2;// More helpers here let PROCRESULTS = (...OUTPARAMS) => JSON.stringify([...OUTPARAMS,[..._OUTQUERIES]]);//END REGION var SQL_CMD =` `; var SQL_CMD_1 =` `; var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true); SQL_CMD =`SELECT * FROM PUBLIC.EMPLOYEE`; var FIRSTSTATEMENT = SQL_CMD; RESULTSET.OPEN();EXEC(`(SELECT CAST(TRUNC(AVG(AGE)) AS int) FROM PUBLIC.EMPLOYEE)`,[]); var subQueryVariable0 = res.getColumnValue(1); P1 = subQueryVariable0;return PROCRESULTS(P1);$$;
In this converted SQL, there are several conversions that take place:
The DYNAMIC RESULT SETS 2 definition is converted to a DYNAMIC_RESULTS variable.
varDYNAMIC_RESULTS=2;
When a cursor with an WITH RETURNattribute is opened (and therefore a query is executed), its query ID is stored in the_OUTQUERIEScollection in order to be later returned. The query id is obtained by thegetQueryId()function provided in the JavaScript API for Snowflake stored procedures.
Only the first k-query-IDs are stored in the collection, where k is the value of theDYNAMIC_RESULTSvariable. This is done to emulate Teradata's behavior, which only returns the first k-opened-cursors, even if more are opened in the stored procedure.
The combination of DECLARE CURSOR WITH RETURN with PREPARE is translated to:
The output parameters are supported via the return statement of the procedure. An array is created containingthe value of each output parameter and the_OUTQUERIEScollection. ThePROCRESULTSfunction deals with the creation and filling of this array. See PROCRESULTS() helper for more information.
returnPROCRESULTS(P1);
Example of getting data from a Stored Procedure
If the output parameters and the query IDs are returned from a procedure, a second one could call the first one to get these values, as shown below:
Teradata
CREATEPROCEDURE Procedure2()BEGINDECLARE x INTEGER;CALL Procedure1(x);END;
Snowflake
CREATEORREPLACEPROCEDURE PUBLIC.Procedure2 ()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var X;EXEC(`CALL PUBLIC.spSample2(?)`,[X]); !SQLCODE && ([X,resultset] = JSON.parse(INTO()));$$;
The value of theP1argument fromProcedure1is returned and stored in theXvariable.
The_OUTQUERIESreturned fromProcedure1are stored in theresultsetvariable.
Known Issues
No issues were found.
Related EWIs
MSCEWI1023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT.