Procedures
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:
Translated Code:
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
andERROR_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, theUPDATE_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:
Simple EXEC example
This is a simple example of an EXEC call inside a Stored Procedure
Source Code
Expected code