This section documents the transformation of the syntax and the procedure's TSQL statements to snowflake java script
Some parts in the output code are omitted for clarity reasons.
1. CREATE PROCEDURE Translation
Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.
Source Code:
IN -> SqlServer_01.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE HumanResources.uspGetAllEmployees
@FirstName NVARCHAR(50),
@Age INT
AS
-- TSQL Statements and queries...
GO
Translated Code:
OUT -> SqlServer_01.sql
CREATE OR REPLACE PROCEDURE HumanResources.uspGetAllEmployees (FIRSTNAME STRING, AGE INT)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
$$;
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
IN -> SqlServer_02.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_1
AS
EXECUTE('SELECT 1 AS Message');
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_1
GO
Expected code
OUT -> SqlServer_02.sql
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'dbo', 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 = '';
} else if (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 REGION
EXEC(`SELECT 1 AS Message;`);
$$;
EXEC within a Store Procedure with a parameter
In this example, the EXEC command is inside a Stored Procedure and receives a parameter value
Source Code
IN -> SqlServer_03.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_2
@p1 varchar(50) = N''
AS
EXEC ('SELECT ' + @p1);
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_2 N'''Hello World!'''
GO
Expected Code
OUT -> SqlServer_03.sql
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_2 (P1 STRING DEFAULT '')
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`SELECT
${P1};`);
$$;
EXEC invoking a Store Procedure with a parameter
In this example, the EXEC invokes another Stored Procedure and pass adds a parameter
Source Code
IN -> SqlServer_04.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_3
@p1 varchar(50) = N''
AS
EXEC EXEC_EXAMPLE_2 @p1
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_3 N'''Hello World!'''
GO
Expected Code
OUT -> SqlServer_04.sql
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_3 (P1 STRING DEFAULT '')
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`CALL EXEC_EXAMPLE_2(?)`,[P1]);
$$;
Parameters with Default Value.
In SqlServer, there can be parameters with a default value in case these are not specified when a procedure is being called.
For example
CREATE PROCEDURE PROC_WITH_DEFAULT_PARAMS1
@PARAM1 INT = 0, @PARAM2 INT = 0, @PARAM3 INT = 0, @PARAM4 INT = 0
AS
BEGIN
.
.
.
END
In Snowflake is translated to
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1(param1 int default 0, param2 int default 0, param3 int default 0, param4 int default 0)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
.
.
.
$$;
CREATE OR REPLACE PROCEDURE PROC1()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var CURSOR = function (stmt, binds) {
var statementObj, result_set, total_rows, isOpen = false, self = this, row_count;
this.CURRENT = new Object;
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
this.OPEN = function(openParameters) {
if (result_set == undefined) {
try {
if (openParameters) binds = openParameters;
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);
}
else {
isOpen = true;
}
}
return this;
};
this.CURSOR_ROWS = function () {
return total_rows;
};
this.FETCH_STATUS = function() {
if(total_rows > row_count)
return 0;
else
return -1;
};
this.FETCH_NEXT = function() {
self.res = [];
if (isOpen) {
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 = false;
};
this.DEALLOCATE = function() {
this.CURRENT = row_count = result_set_table = total_rows = result_set = statementObj = self = undefined;
};
};
var COL1, COL2;
var sql_stmt = ``;
let c = new CURSOR(`SELECT COL1, COL2 FROM TABLE1;`,() => []);
c.OPEN();
c.FETCH_NEXT();
[COL1, COL2] = c.INTO();
while ( c.FETCH_STATUS()) {
sql_stmt = `INSERT INTO TABLE2 (COL1, COL2) VALUES (` + COL1+ `, ` + COL2 + `)`;
snowflake.createStatement({
sqlText : sql_stmt
}).execute();
}
c.CLOSE();
c.DEALLOCATE();
return 'sucess';
$$;
Insert Into EXEC Helper
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";
return EXEC('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
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
IF @VariableValue like '%c%'
BEGIN
Select 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.
function LIKE(expr,pattern,esc,cs) {
function fixPattern(pattern,esc) {
const specials = '/.*+?|(){}[]\\'.split('');
var newPattern = "";
var fix = (c) => specials.includes(c) ? '\\' + c : c;
for(var i = 0;i < pattern.length;i++) {
var c = pattern[i];
if (c === esc) {
newPattern += pattern[i + 1]
i++
} else if (c === '%') {
newPattern += ".*?"
} else if (c === '_') {
newPattern += "."
} else if (c === '[' || ']') {
newPattern += c
} else newPattern += fix(c)
}
return newPattern;
}
return new RegExp(`^${fixPattern(pattern,esc)}$`,cs ? '' : 'i').exec(expr) != null;
}
With this function, we can replicate the functionality of the like expression of sql. Let's see the diferent cases that it can be used
IN -> SqlServer_05.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
IF @VariableValue like '%c%'
BEGIN
Select AValue from ATable;
END;
IF @VariableValue not like '%c%'
BEGIN
Select BValue from BTable;
END;
IF @VariableValue like '%c!%%' escape '!'
BEGIN
Select CValue from CTable;
END;
END;
In the last code, there is a normal like a not like, and a like with escape. The transformation will be
OUT -> SqlServer_05.sql
CREATE OR REPLACE PROCEDURE ProcedureLike (VARIABLEVALUE STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
if (LIKE(VARIABLEVALUE,`%c%`)) {
{
EXEC(` Select
AValue
from
ATable`);
}
}
if (!LIKE(VARIABLEVALUE,`%c%`)) {
{
EXEC(` Select
BValue
from
BTable`);
}
}
if (LIKE(VARIABLEVALUE,`%c!%%`,`!`)) {
{
EXEC(` Select
CValue
from
CTable`);
}
}
$$;
Note that the likes are transformed to function calls
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
IN -> SqlServer_06.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE MAX_EMPLOYEE_ID
AS
BEGIN
DECLARE @VARIABLE INT
SET @VARIABLE = (SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES);
RETURN @VARIABLE
END;
In this case, it will generate the following code with the SELECT helper
OUT -> SqlServer_06.sql
CREATE OR REPLACE PROCEDURE MAX_EMPLOYEE_ID ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VARIABLE;
VARIABLE = SELECT(` MAX(EMPLOYEE_ID) FROM
EMPLOYEES`);
return VARIABLE;
$$;
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:
IN -> SqlServer_07.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE [PROCEDURE1] AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
select @VAR1 = col1 + col2, @VAR2 += col1 from table1;
GO
In this case the variable assignments will be translated to JavaScript lambdas in order to emulate the SQL Server behavior.
OUT -> SqlServer_07.sql
CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VAR1;
let VAR2;
SELECT(` col1 + col2,
col1
from
table1`,[],(value) => VAR1 = value,(value) => VAR2 += value);
$$;
RAISERROR Helper
This helper is generated when there exists usages of a RAISERROR call in the source code. 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}`";
return EXEC(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.
The purpose of this helper is to encapsulate the logic required for calling procedures as if they were functions.
Please keep in mind that this functionality is limited, since procedures cannot be invoked within queries such as SELECT.
Example of use, assuming that FooSelfAssign(@PAR INT) was translated to a procedure:
// Input code
DECLARE @VAR1 INT = FooSelfAssign(1);
DECLARE @VAR4 INT = FooSelfAssign(FooSelfAssign(FooSelfAssign(FooSelfAssign(4))));
// Output code
let VAR1 = CALL(`FooSelfAssign(1)`)
let VAR4 = CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(4)`)])])]);
Note that the translation for VAR1 is very straightforward, but for VAR4, the outmost CALL contains a list with the rest of the CALLs, as bindings.
Each successive CALL is translated to a binding, if it's contained within another CALL.
2. Variables
DECLARE @Variable
Source Code
DECLARE @product_list VARCHAR(MAX) = ' ';
DECLARE @Variable1 AS VARCHAR(100), @Variable2 AS VARCHAR(100);
Translated Code
let PRODUCT_LIST = ` `;
let VARIABLE1;
let VARIABLE2;
DECLARE @Variable Table
In this case, the DECLARE is used to declare a variable table, let's see an example.
IN -> SqlServer_08.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
DECLARE @VariableNameTable TABLE
(
[Col1] INT NOT NULL,
[Col2] INT NOT NULL
);
INSERT INTO @VariableNameTable Values(111,222);
Select * from @VariableNameTable;
END
Exec PROC1;
If we execute that code in Sql Server, we will get the following result
Now, let's see the transformation in Snowflake
OUT -> SqlServer_08.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
{
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_VariableNameTable
(
Col1 INT NOT NULL,
Col2 INT NOT NULL
)`);
EXEC(`INSERT INTO T_VariableNameTable Values(111,222)`);
EXEC(`Select
*
from
T_VariableNameTable`);
}
EXEC(`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.
Example
IN -> SqlServer_09.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
SET @product_list2 = '';
SET @product_list = '';
SET @var1 += '';
SET @var2 &= '';
SET @var3 ^= '';
SET @var4 |= '';
SET @var5 /= '';
SET @var6 %= '';
SET @var7 *= '';
SET @var8 -= '';
SET @ProviderStatement = 'SELECT * FROM TABLE1
WHERE COL1 = '+@PARAM1+ ' AND COL2 = ' + @LOCALVAR1;
SET @NotSupported = functionValue(a,b,c);
END
Translated Code
OUT -> SqlServer_09.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
PRODUCT_LIST2 = ``;
PRODUCT_LIST = ``;
VAR1 += ``;
VAR2 &= ``;
VAR3 ^= ``;
VAR4 |= ``;
VAR5 /= ``;
VAR6 %= ``;
VAR7 *= ``;
VAR8 -= ``;
PROVIDERSTATEMENT = `SELECT
*
FROM
TABLE1
WHERE
COL1 = ${PARAM1}
AND COL2 = ${LOCALVAR1};`;
NOTSUPPORTED = SELECT(` !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'functionValue' NODE ***/!!!
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
IN -> SqlServer_10.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;
SET NOCOUNT ON
;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;
SET NOCOUNT OFF
;
END
Translated Code
OUT -> SqlServer_10.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET NOCOUNT ON*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - 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.
Input
IN -> SqlServer_11.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
Output
OUT -> SqlServer_11.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VAR1;
let VAR2;
SELECT(` COL1 + COL2,
COL3
FROM
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 Statement
ELSE IF Conditiona_Expression2
-- SQL Statement
ELSE
-- SQL Statement
Translated Code
if (Conditional_Expression) {
// SQL Statement
} else if (Conditional_Expression2) {
// SQL Statement
} else{
// SQL Statement
}
WHILE
Source Code
WHILE ( Conditional_Expression )
BEGIN
-- SQL STATEMENTS
END;
Translated Code
while ( Conditional_Expression )
{
// SQL STATEMENTS
}
-- Execute simple statement
EXEC(`Select 1`);
-- Execute statement using Dynamic Sql
EXEC(`Select ${PAR1} from MYDB.db.t1`);
-- Execute Procedure with parameter
EXEC(`CALL db.sp2(/*** SSC-EWI-0038 - 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
IN -> SqlServer_12.sql
-- Additional Params: -t JavaScript
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
OUT -> SqlServer_12.sql
CREATE OR REPLACE PROCEDURE RAISERRORTEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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:
IN -> SqlServer_13.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
Continue;
ELSE
BREAK;
END
Will be transformed to:
OUT -> SqlServer_13.sql
CREATE OR REPLACE PROCEDURE ProcSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
if (ROW_COUNT > 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) /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*WAITFOR TIME '13:30'*/
;
3) /*** SSC-EWI-0040 - 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 functionality to a JavaScript helper that emulates the original behavior in the target platform. Example:
Input:
IN -> SqlServer_14.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procCursorHelper] AS
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;
GO
Output:
OUT -> SqlServer_14.sql
CREATE OR REPLACE PROCEDURE procCursorHelper ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> SqlServer_15.sql
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels]
AS
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
Translated Code
OUT -> SqlServer_15.sql
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> SqlServer_16.sql
-- Additional Params: -t JavaScript
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
OUT -> SqlServer_16.sql
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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 .
