This section documents the transformation of the syntax and the procedure's TSQL statements to snowflake java script
1. CREATE PROCEDURE Translation
Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.
Source Code:
CREATEPROCEDURE HumanResources.uspGetAllEmployees @FirstName NVARCHAR(50), @Age INTAS-- TSQL Statements and queries...GO
Translated Code:
CREATEORREPLACEPROCEDURE P1 (FirstName VARCHAR(50), Age INT)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS $$-- Embbeded TSQL code to javascript... $$
Parameter's DATA TYPE
Parameters data types are being translated to Snowflake equivalent. See also Data Types.
EXEC helper
In order to be able to run statements from a procedure in the SnowFlake environment, these statements have to be preprocessed and adapted to reflect their execution in several variables that are specific to the source language.
SnowConvert automatically translates the supported statements and makes use of an EXEC helper. This helper provides access and update capabilities to many variables that simulate how the execution of these statements would be in their native environment.
For instance, you may see that in the migrated procedures, there is a block of code that is always added. We are going to explain the basic structure of this code in the next section. Please keep in mind that we are always evaluating and searching for new and improved ways to streamline the transformations and any helper that we require.
Structure
The basic structure of the EXEC helper is as follows:
Variable declaration section: Here, we declare the different variables or objects that will contain values associated with the execution of the statements inside the procedure. This includes values such as the number of rows affected by a statement, or even the result set itself.
fixBind function declaration: This is an auxiliary function used to fix binds when they are of Date type.
EXEC function declaration: This is the main EXEC helper function. It receives the statement to execute, the array of binds (basically the variables or parameters that may be modified by the execution and require data permanence throughout the execution of the procedure), the noCatch flag that determines if the ERROR_HANDLERS must be used, and the catchFunction function for executing custom code when there's an exception in the execution of the statement. The body of the EXEC function is very straightforward; execute the statement and store every valuable data produced by its execution, all inside an error handling block.
ERROR VARS: The EXEC catch block sets up a list of error variables such as MESSAGE_TEXT, SQLCODE, SQLSTATE, PROC_NAME and ERROR_LINE that could be used to retrieve values from user defined functions, in order to emulate the SQL Server ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE and ERROR_STATE built in functions behavour. After all of these variables are set with one value, the UPDATE_ERROR_VARS user defined function, will be in charge of update some environment variables with the error values, in order to have access to them in the SQL scope.
Code
The following code block represents the EXEC helper inside a procedure:
This is a simple example of an EXEC call inside a Stored Procedure
Source Code
CREATEPROCEDURE dbo.EXEC_EXAMPLE_1ASEXECUTE('SELECT 1 AS Message');GO-- =============================================-- Example to execute the stored procedure-- =============================================EXECUTE dbo.EXEC_EXAMPLE_1GO
As you can see, in ProcTest the parameters @param2 and @param4 have a default value.
EXEC ProcTest @param = 'a', @param3 = 'c';
In Snowflake, there are no default values for parameters. so the start of the procedure is translated to
CREATEORREPLACEPROCEDURE ProcTest (PARAM STRING, /*** MSC-WARNING - MSCEWI4009 - The default value 'Param2 Default' is not supported by Snowflake. ***/ PARAM2 STRING, PARAM3 STRING, /*** MSC-WARNING - MSCEWI4009 - The default value 'Param4 Default' is not supported by Snowflake. ***/ PARAM4 STRING)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS
As you can see, there is not such thing as a default value in the procedure declaration, so how is it going to work? When the Exec statement is translated, it will check if the procedure's parameters had default values, if such thing is found, then it will translate the EXEC statement to
The Insert into Exec helper generates a function called Insert insertIntoTemporaryTable(sql). This function will allow the transformation for INSERT INTO TABLE_NAME EXEC(...) from TSQL to Snowflake to imitate the behavior from the original statement by inserting it's data into a temporary table and then re-adding it into the original Insert.
For more information on how the code for this statement is modified look at the section for Insert Into Exec
This Generated code for the INSERT INTO EXEC, may present performance issues when handling EXECUTE statements containing multiple queries inside.
function insertIntoTemporaryTable(sql) { var table="SnowConvertPivotTemporaryTable";returnEXEC('CREATE OR REPLACE TEMPORARY TABLE ${table} AS ${sql}'); } insertIntoTemporaryTable(`${DBTABLES}`)EXEC(`INSERT INTO MYDB.PUBLIC.T_Table SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
LIKE Helper
In case that a like expression is found in a procedure, for example
CREATEPROCEDURE ProcedureLike @VariableValue VARCHAR(50) ASBEGINIF @VariableValue like'%c%'BEGINSelect AValue from ATable;END;END;
Since the inside of the procedure is transformed to javascript, the like expression will throw an error. In order to avoid and keep the functionality, a function is added at the start of the procedure if a like expression is found.
The parameters that the function LIKE receive are the followings:
The expression that is being evaluated.
The pattern of comparison
If it is present, the escape character, this is an optional parameter.
Select Helper
Generates a function called SELECT when a scalar value has to be set to a variable
input.sql
CREATEPROCEDURE MAX_EMPLOYEE_IDASBEGINDECLARE @VARIABLE INTSET @VARIABLE = (SELECTMAX(EMPLOYEE_ID) FROM EMPLOYEES);RETURN @VARIABLEEND;
In this case, it will generate the following code with the SELECT helper
output.sql
CREATEORREPLACEPROCEDURE MAX_EMPLOYEE_ID()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var SELECT= (sql,binds = [],...args) => { var reducers = args.filter((i) => i instanceof Function); reducers = reducers.length ? reducers : [(value) => value] args = args.splice(0,args.length - reducers.length)EXEC("SELECT "+sql,binds)if (ROW_COUNT <1) return; var colCount = _ROWS.getColumnCount();if (colCount != reducers.length) throw new Exception("Missing arguments results has ${colCount} columns"); var cols = Array.from(Array(colCount),() => []);while ( _ROWS.next() ) {for(var i =0;i < colCount;i++) { cols[i].push(_ROWS.getColumnValue(i +1)) } }if (colCount ==1) { cols[0].forEach((value) => reducers[0](value))return cols[0]; }for(var i =0;i < colCount;i++) { cols[i].forEach((value) => reducers[i](value)) } }; var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE =0, SQLSTATE ='00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, INTO; var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() *60000))).toISOString().slice(0,-1); var fixBind =function (arg) { arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;return arg; }; var EXEC= (stmt,binds = [],noCatch = false) => {//EXEC IMPLEMENTATION };//END REGION let VARIABLE; VARIABLE =SELECT(`MAX(EMPLOYEE_ID) FROM PUBLIC.EMPLOYEES`);$$;
The SELECT helper could be used as well to insert into a local value a retrieved value from a query. The helper was designed specifically to support the same behavour of the SQL Server SELECT @local_variable. The args parameter, represents each operation applied to all of the local variables inside the select. See also SELECT @Variable. For example:
The RAISERROR executes the UPDATE_ERROR_VARS_UDF in order to store the value of the error message, severity and state as environment variables, in case they need to be used by calling any of the ERROR built in functions. Finally, the error message is thrown with the same format as SQL Server does.
Identity Function Helper
This helper is generated whenever the Identity Fuction is used on a Select Into inside a procedure.
var IdentityHelper = (seed,increment) => { var sequenceString = "`CREATE OR REPLACE SEQUENCE SnowConvert_Temp_Seq START = ${seed} INCREMENT = ${increment}`";
returnEXEC(sequenceString);
The parameters for this helper are the same as the original function, it is created in order to generate a sequence to mimic the identity function behavior in TSQL, the changes to the original code are:
An additional method call to the IdentityHelper function using the same parameters found in the source code.
And call to the IDENTITY_UDF a function design to get the next value in the sequence.
IdentityHelper(1,1) EXEC(`CREATE TABLE PUBLIC.department_table3 AS SELECT IDENTITY_UDF() /*** MSC-WARNING - MSCEWI1046 - 'identity' FUNCTION MAPPED TO 'IDENTITY_UDF', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ as Primary_Rank
from PUBLIC.department_table`);
Just like in the TSQL if no parameters are given (1,1) will be the default values.
CALL Procedure Helper
This helper is generated whenever there is a call to what previously was a user defined function, but is now a procedure as a result of the translation process.
If we execute that code in Sql Server, we will get the following result
Now, let's see the transformation in Snowflake
CREATEORREPLACEPROCEDURE PROC1 ()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$// REGION SnowConvert Helpers Code var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) { var part =''; var ismark = () =>sql[i] =='$' && sql[i + 1] =='$';for(var i =0;i < sql.length;i++) {if (sql[i] ==';') { yield part +sql[i]; part =''; } elseif (ismark()) { part +=sql[i++] +sql[i++];while ( i < sql.length && !ismark() ) { part +=sql[i++]; } part +=sql[i] +sql[i++]; } else part +=sql[i]; }if (part.trim().length) yield part; }; var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() *60000))).toISOString().slice(0,-1); var fixBind =function (arg) { arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;return arg; }; var EXEC= (stmt,binds = [],severity ="16",noCatch = false) => { binds = binds ? binds.map(fixBind) : binds;for(var stmt of sqlsplit(stmt)) {try { _RS = snowflake.createStatement({ sqlText : stmt, binds : binds }); _ROWS = _RS.execute(); ROW_COUNT = _RS.getRowCount(); NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();return {THEN : (action) => !SQLCODE && action(fetch(_ROWS)) }; } catch(error) { let rStack = new RegExp('At .*, line (\\d+) position (\\d+)'); let stackLine = error.stackTraceTxt.match(rStack) || [0,-1]; MESSAGE_TEXT = error.message.toString(); SQLCODE = error.code.toString(); SQLSTATE = error.state.toString(); snowflake.execute({ sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`, binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity] });throw error; } } };//END REGIONEXEC(`CREATE OR REPLACE TEMPORARY TABLE PUBLIC.T_VariableNameTable(Col1 INT NOT NULL,Col2 INT NOT NULL)`);EXEC(`INSERT INTO PUBLIC.T_VariableNameTable Values(111,222)`);EXEC(`Select * from PUBLIC.T_VariableNameTable`);$$;CALL PROC1();
Note that from the lines 61 to 67 are the results of those statements inside the procedure.
The Declare Variable Table is turned into a Temporary Table. Note that the name, which that in the name the character @ was replaced for T_.
If we execute that code in Snowflake, we will not get any result. it will display just null. That's because that last Select is now in the EXEC helper. So, how do we know that the table is there?
Since it was created as a temporary table inside the Procedure in an EXEC, we can do a Select to that table outside of the Procedure.
Select*from PUBLIC.T_VariableNameTable;
If we execute that statement, we will get the following result
SET @Variable
For now, the Set Variable is transformed depending on the expression that is has on the right side.
If the expression has a transformation, it will be transformed to it's JavaScript equivalent.
PRODUCT_LIST2=``;PRODUCT_LIST=``;VAR1+=``;VAR2&=``;VAR3^=``;VAR4|=``;VAR5/=``;VAR6%=``;VAR7*=``;VAR8-=``;PROVIDERSTATEMENT=`SELECT * FROM TABLE1WHERE COL1 = ${PARAM1} AND COL2 = ${LOCALVAR1}`;NOTSUPPORTED= defaultExpression// functionValue(a,b,c) ;
As you can see in the example, the value of the variable NOTSUPPORTED is commented since it is not being transformed for the time being. Note that means that the transformation is not completed yet.
Other kinds of sets are commented, for example the following
/*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***//* SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/ ;/*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***//* SET NOCOUNT ON*/ ;/*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***//* SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/ ;/*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***//* SET NOCOUNT OFF*/ ;
SELECT @Variable
For now, the SELECT @variable is being transformed into a simple select, removing the variable assignations, and keeping the expressions at the right side of the operator. The assignment operations of the local variables in the select, will be replaced with arrow functions that represent the same behavour of the operation being did during the local variable assignment in SQL Server.
CREATEORREPLACEPROCEDURE PROC1()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTEASCALLERAS$$ var SELECT= (sql,binds = [],...args) => {// SOME IMPLEMENTATIONS HERE }; let VAR1; let VAR2;SELECT(`col1 + col2,col3 from PUBLIC.table1`,[],(value) => VAR1 =value,(value) => VAR2 +=value);$$;
3. Statements translation
SELECT
Basic form
The basic SELECT form does not have bindings, so the translation implies the creation of a call to the EXEC helper function, with one parameter.
For example:
-- Source code:SELECT*FROM DEMO_TABLE_1;
// Translated code:EXEC(`SELECT * FROM DEMO_TABLE_1`);
IF
Source Code
IF Conditional_Expression-- SQL StatementELSEIF Conditiona_Expression2-- SQL StatementELSE-- SQL Statement
Translated Code
if (Conditional_Expression) {//SQLStatement} elseif (Conditional_Expression2) {//SQLStatement} else{//SQLStatement}
WHILE
Source Code
WHILE ( Conditional_Expression )BEGIN-- SQL STATEMENTSEND;
Translated Code
while ( Conditional_Expression ){// SQL STATEMENTS}
EXEC / EXECUTE
Source code
-- Execute simple statementExec('Select 1');-- Execute statement using Dynamic SqlExec('Select '+ @par1 +' from [db].[t1]');-- Execute Procedure with parameterEXEC db.sp2 'Create proc [db].[p3] AS', @par1, 1
Translated Code
-- Execute simple statementEXEC(`Select 1`);-- Execute statement using Dynamic SqlEXEC(`Select ${PAR1} from MYDB.db.t1`);-- Execute Procedure with parameterEXEC(`CALL db.sp2(/*** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED ***/
'Select * from MYDB.db.t1', ?, 1, Default)`,[PAR1]);
THROW
The transformation for THROW ensures that the catch block that receives the error has access to the information specified in the original statement.
For instance:
-- Case 1
THROW
-- Case 2
THROW 123, 'The error message', 1
-- Case 3
THROW @var1, @var2, @var3
Will be transformed to:
// Case 1
throw {};
// Case 2
throw { code: 123, message: "The error message", status: 1 };
// Case 3
throw { code: VAR1, message: VAR2, status: VAR3 };
RAISERROR
SQL Server RAISERROR function is not supported in Snowflake. SnowConvert identifies all the usages in order to generate a helper that emulates the original behavour. Example:
From
CREATE OR ALTER PROCEDURE RAISERRORTEST AS
BEGIN
DECLARE @MessageTXT VARCHAR = 'ERROR MESSAGE';
RAISERROR (N'E_INVALIDARG', 16, 1);
RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
RAISERROR(@MessageTXT, 16, 1);
END
GO
To
CREATE OR REPLACE PROCEDURE RAISERRORTEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var RAISERROR = (message,severity,state) => {
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?)`,
binds : [message,severity,state]
});
var msg = `Message: ${message}, Level: ${severity}, State: ${state}`;
throw msg;
};
// END REGION
let MESSAGETXT = `ERROR MESSAGE`;
RAISERROR("E_INVALIDARG","16","1");
RAISERROR("Diagram does not exist or you do not have permission.","16","1");
RAISERROR(MESSAGETXT,"16","1");
$$;
BREAK/CONTINUE
The break/continue transformation, ensures flow of the code to be stopped or continue with another block.
For instance:
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
Continue;
ELSE
BREAK;
END
Will be transformed to:
CREATE OR REPLACE PROCEDURE ProcSample()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
// END REGION
if (ROWCOUNT > 0) {
continue;
} else {
break;
}
$$;
INSERT INTO EXEC
The code is modify slightly due to the INSERT INTO [Table] EXEC(...) Statement not being supported in Snowflake this allows us to replicate the behavior by adding a few lines of code:
The first line added is a call to the insertIntoTemporaryTable to where the extracted code from the argument inside the EXEC, this will Insert the result set into a Temporary table. For more information on the function check the Insert Into EXEC Helper section.
The Insert's Exec is removed from the code and a query retrieving the results of the EXEC from the temporary table.
SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
The last line added is a DROP TABLE statement for the Temporary Table added.
DROP TABLE SnowConvertPivotTemporaryTable
Source Code:
INSERT INTO #Table1
EXEC ('SELECT
Table1.ID
FROM Population');
INSERT INTO #Table1
EXEC (@DBTables);
Translated Code:
insertIntoTemporaryTable(`SELECT Table1.ID FROM MYDB.PUBLIC.Population)
EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
insertIntoTemporaryTable(`${DBTABLES}`)
EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
BEGIN TRANSACTION
BEGIN TRANSACTION is transformed to Snowflake's BEGIN command, and inserted into an EXEC helper call.
The helper is in charge of actually executing the resulting BEGIN.
Example:
-- Input code
BEGIN TRAN @transaction_name;
// Output code
EXEC(`BEGIN`, []);
COMMIT TRANSACTION
COMMIT TRANSACTION is transformed to Snowflake's COMMIT command, and inserted into an EXEC helper call.
The helper is in charge of actually executing the resulting COMMIT.
Example:
-- Input code
COMMIT TRAN @transaction_name;
// Output code
EXEC(`COMMIT`, []);
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION is transformed to Snowflake's ROLLBACK command, and inserted into an EXEC helper call.
The helper is in charge of actually executing the resulting ROLLBACK .
Example:
-- Input code
ROLLBACK TRAN @transaction_name;
// Output code
EXEC(`ROLLBACK`, []);
WAITFOR DELAY
WAITFOR DELAY clause is transformed to Snowflake's SYSTEM$WAIT function. The time_to_pass parameter of the DELAY is transformed to seconds, for usage as a parameter in the SYSTEM$WAIT function.
The other variants of the WAITFOR clause are not supported in Snowflake, and are therefore marked with the corresponding message.
Example:
-- Input code
1) WAITFOR DELAY '02:00';
2) WAITFOR TIME '13:30';
3) WAITFOR (RECEIVE TOP (1)
@dh = conversation_handle,
@mt = message_type_name,
@body = message_body
FROM [eqe]), TIMEOUT 5000;
// Output code
1) EXEC(`SYSTEM$WAIT(120)`,[]);
2) /*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/* WAITFOR TIME '13:30' */
;
3) /*** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/* WAITFOR (RECEIVE TOP (1)
@dh = conversation_handle,
@mt = message_type_name,
@body = message_body
FROM [eqe]), TIMEOUT 5000 */
;
3. Cursors
Since CURSORS are not supported in Snowflake, SnowConvert maps their functionallity to a JavaScript helper that emulates the original behavour in the target platform. Example:
Input:
CREATE PROCEDURE [procCursorHelper] AS
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;
GO
Output:
CREATE OR REPLACE PROCEDURE procCursorHelper ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var CURSOR = function (stmt,binds) {
var statementObj, result_set, total_rows, isOpen = false, result_set_table = '', self = this;
this.CURRENT = new Object;
this.OPEN = function (usingParams) {
try {
if (usingParams) binds = usingParams;
if (binds instanceof Function) binds = binds();
var finalBinds = binds && binds.map(fixBind);
var finalStmt = stmt instanceof Function ? stmt() : stmt;
statementObj = snowflake.createStatement({
sqlText : finalStmt,
binds : finalBinds
});
result_set = statementObj.execute();
total_rows = statementObj.getRowCount();
isOpen = true;
row_count = 0;
} catch(error) {
RAISE(error.code,"error",error.message);
}
return this;
};
this.CURSOR_ROWS = function () {
return total_rows;
};
this.FETCH_STATUS = function () {
return total_rows > row_count;
};
this.FETCH_NEXT = function () {
self.res = [];
self.res = fetch(total_rows,result_set,statementObj);
if (self.res) row_count++
return self.res && self.res.length > 0;
};
this.INTO = function () {
return self.res;
};
this.CLOSE = function () {
isOpen = row_count = result_set_table = total_rows = result_set = statementObj = undefined;
};
this.DEALLOCATE = function () {
this.CURRENT = self = undefined;
};
};
// END REGION
var VENDOR_CURSOR = new CURSOR(`SELECT VendorID,
Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID`,[],false);
$$;
DECLARE CURSOR
For now, the Declare Cursor is just being commented.
Source Code
DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
Translated Code
let myCursor1 = new CURSOR(`SELECT COL1 FROM TABLE1`,() => []);
OPEN
Source Code
OPEN myCursor1
OPEN GLOBAL myCursor2
Translated Code
myCursor1.OPEN();
myCursor2.OPEN()
FETCH
Source Code
DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1
Translated Code
var VALUE1;
myCursor1.FETCH_NEXT();
VALUE1 = myCursor1.INTO();
CLOSE
Source Code
CLOSE myCursor1
CLOSE GLOBAL myCursor2
Translated Code
myCursor1.CLOSE()
myCursor2.CLOSE()
DEALLOCATE
Source Code
DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
Translated Code
myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
@@FETCH_STATUS
Source Code
@@FETCH_STATUS
Translated Code
myCursor1.FETCH_STATUS()
@@CURSOR_ROWS
Source Code
@@CURSOR_ROWS
Translated Code
myCursor1.FETCH_STATUS()
4. Expressions
Binary Operations
Source Code
SET @var1 = 1 + 1;
SET @var1 = 1 - 1;
SET @var1 = 1 / 1;
SET @var1 = 1 * 1;
SET @var1 = 1 OR 1;
SET @var1 = 1 AND 1;
Labels have not the same behavior in JavaScript as SQL Server has. To simulate the behavior, they are being transformed to functions . Its usage is being replaced with a call of the generated function that contains all the logic of the label. Example:
Source Code
CREATE PROCEDURE [procWithLabels]
AS
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
Translated Code
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
// END REGION
SUCCESS_EXIT();
ERROR_EXIT();
function SUCCESS_EXIT() {
ERRORSTATUS = 0;
return ERRORSTATUS;
}
function ERROR_EXIT() {
return ERRORSTATUS;
}
$$;
As you see in the example above, the function declarations that were the labels in the source code, will be put at the end of the code in order to make it cleaner.
GOTO is another command that does not exist in JavaScript. To simulate its behavour, their usages are being transformed to calls to the function (label) that is referenced, preceded by a return statement. Example:
Source Code
CREATE PROCEDURE [procWithLabels]
AS
DECLARE @ErrorStatus int = 0;
IF @ErrorStatus <> 0 GOTO ERROR_EXIT
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
Translated Code
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
// END REGION
let ERRORSTATUS = 0;
if (ERRORSTATUS != 0) {
return ERROR_EXIT();
}
SUCCESS_EXIT();
ERROR_EXIT();
function SUCCESS_EXIT() {
ERRORSTATUS = 0;
return ERRORSTATUS;
}
function ERROR_EXIT() {
return ERRORSTATUS;
}
$$;
As you see in the example above, the return is added to the function call, in order to stop the code flow as SQL Server does with the GOTO .