SnowConvert: Transact DDLs

Translation reference for all the DDL statements that are preceded by the CREATE word.

Index

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Warning

Currently, Create Index statement is not being converted but it is being parsed. Also, if your source code has Create index statements, these are going to be accounted for in the Assessment Report.

Example of Create Index

 CREATE INDEX my_index_name ON my_table (column1, column2);
Copy

Note

Due to architectural reasons, Snowflake does not support indexes so, SnowConvert will remove all the code related to the creation of indexes. Snowflake automatically creates micro-partitions for every table that help speed up the performance of DML operations, the user does not have to worry about creating or managing these micro-partitions.

Usually, this is enough to have a very good query performance however, there are ways to improve it by creating data clustering keys. Snowflake’s official page provides more information about micro-partitions and data clustering.

Materialized View

Applies to
  • [x] Azure Synapse Analytics

Description

In Snowconvert, Materialized Views are transformed into Snowflake Dynamic Tables. To properly configure Dynamic Tables, two essential parameters must be defined: TARGET_LAG and WAREHOUSE. If these parameters are left unspecified in the configuration options, Snowconvert will default to preassigned values during the conversion, as demonstrated in the example below.

For more information on Materialized Views, click here.

For details on the necessary parameters for Dynamic Tables, click here.

Sample Source Patterns

SqlServer

 CREATE MATERIALIZED VIEW sales_total
AS
SELECT SUM(amount) AS total_sales
FROM sales;
Copy

Snowflake

 CREATE OR REPLACE DYNAMIC TABLE sales_total
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT SUM(amount) AS total_sales
FROM
sales;
Copy

Known Issues

No known errors detected at this time.

Procedures

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

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:

 -- Additional Params: -t JavaScript
CREATE PROCEDURE HumanResources.uspGetAllEmployees
     @FirstName NVARCHAR(50),
     @Age INT
AS
    -- TSQL Statements and queries...
GO
Copy

Translated Code:

 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.
$$;
Copy

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:

  1. 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.

  2. fixBind function declaration: This is an auxiliary function used to fix binds when they are of Date type.

  3. 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.

  4. 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:

    var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, INTO;
   var fixBind = function (arg) {
      arg = arg == undefined ? null : arg instanceof Date ? arg.toISOString() : arg;
      return arg;
   };
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var EXEC = (stmt,binds = [],noCatch = false) => {
      binds = binds ? binds.map(fixBind) : binds;
      for(var stmt of stmt.split(";").filter((_) => _)) {
         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]});
            throw error;
         }
      }
   };
Copy

Simple EXEC example

This is a simple example of an EXEC call inside a Stored Procedure

Source Code

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_1
AS
	EXECUTE('SELECT 1 AS Message');
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_1
GO
Copy

Expected code

 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;`);
$$;
Copy

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

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_2
	@p1 varchar(50) = N''
AS
	EXEC ('SELECT ' + @p1);
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_2 N'''Hello World!'''
GO
Copy

Expected Code

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};`);
$$;
Copy

EXEC invoking a Store Procedure with a parameter

In this example, the EXEC invokes another Stored Procedure and pass adds a parameter

Source Code

 -- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_3
	@p1 varchar(50) = N''
AS
	EXEC EXEC_EXAMPLE_2 @p1
GO
Copy
 -- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_3 N'''Hello World!'''
GO
Copy

Expected Code

 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]);
$$;
Copy

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
Copy

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
$$
    .
    .
    .
$$;
Copy
 CALL PROC_WITH_DEFAULT_PARAMS1(param2 => 10, param4 => 15);
Copy

CURSOR helper

 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';
$$;
Copy

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

Note

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`)
Copy

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;
Copy

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;
  }
Copy

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

 -- 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;
Copy

In the last code, there is a normal like a not like, and a like with escape. The transformation will be

 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`);
		}
	}
$$;
Copy

Note that the likes are transformed to function calls

 LIKE(VARIABLEVALUE,`%c%`)
!LIKE(VARIABLEVALUE,`%c%`)
LIKE(VARIABLEVALUE,`%c!%%`,`!`)
Copy

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

 -- 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;
Copy

In this case, it will generate the following code with the SELECT helper

 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;
$$;
Copy

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:

 -- Additional Params: -t JavaScript
CREATE PROCEDURE [PROCEDURE1] AS

DECLARE @VAR1 int;
DECLARE @VAR2 int;
select @VAR1 = col1 + col2, @VAR2 += col1 from table1;

GO
Copy

In this case the variable assignments will be translated to JavaScript lambdas in order to emulate the SQL Server behavior.

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);
$$;
Copy

RAISERROR Helper

This helper is generated when there exists usages of a RAISERROR call in the source code. Example:

 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;
  };
Copy

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);
Copy

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`);
Copy

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.

    var CALL = (sql,binds = [],...args) => {
      EXEC("CALL " + sql,binds);
      _ROWS.next();
      return (_ROWS.getColumnValue(1))[0];
   };
Copy

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))));
Copy
 // Output code
let VAR1 = CALL(`FooSelfAssign(1)`)
let VAR4 = CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(4)`)])])]);
Copy

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);
Copy

Translated Code

    let PRODUCT_LIST = ` `;
   let VARIABLE1;
   let VARIABLE2;
Copy

DECLARE @Variable Table

In this case, the DECLARE is used to declare a variable table, let’s see an example.

 -- 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;
Copy

If we execute that code in Sql Server, we will get the following result

Now, let’s see the transformation in Snowflake

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

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;
Copy

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

 -- 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
    
Copy

Translated Code

 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)`);
$$;
Copy

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

 -- 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
Copy

Translated Code

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*/
;
$$;
Copy

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
 -- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
Copy
Output
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);
$$;
Copy

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;
Copy
 // Translated code:
EXEC(`SELECT * FROM DEMO_TABLE_1`);
Copy

IF

Source Code

 IF Conditional_Expression
   -- SQL Statement
ELSE IF Conditiona_Expression2
   -- SQL Statement
ELSE
   -- SQL Statement
Copy

Translated Code

 if (Conditional_Expression) {
    // SQL Statement
} else if (Conditional_Expression2) {
    // SQL Statement
} else{
    // SQL Statement
}
Copy

WHILE

Source Code

 WHILE ( Conditional_Expression )
BEGIN
   -- SQL STATEMENTS
END;
Copy

Translated Code

 while ( Conditional_Expression )
{
  // SQL STATEMENTS
}
Copy

EXEC / EXECUTE

Source code

 -- Execute simple statement
Exec('Select 1');

-- Execute statement using Dynamic Sql
Exec('Select ' + @par1 + ' from [db].[t1]');

-- Execute Procedure with parameter
EXEC db.sp2 'Create proc [db].[p3] AS', @par1, 1
Copy

Translated Code

 -- 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]);
Copy

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
Copy

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 };
Copy

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
 -- 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
Copy
To
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");
$$;
Copy

BREAK/CONTINUE

The break/continue transformation, ensures flow of the code to be stopped or continue with another block.

For instance:

-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
  Continue;
ELSE
  BREAK;
END
Copy

Will be transformed to:

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;
  }
$$;
Copy

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
    
Copy
  • The last line added is a DROP TABLE statement for the Temporary Table added.

     DROP TABLE SnowConvertPivotTemporaryTable
    
Copy

Source Code:

 INSERT INTO #Table1
EXEC ('SELECT
Table1.ID
FROM Population');

INSERT INTO #Table1
EXEC (@DBTables);
Copy

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`)
Copy

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;
Copy
 // Output code
EXEC(`BEGIN`, []);
Copy

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;
Copy
 // Output code
EXEC(`COMMIT`, []);
Copy

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;
Copy
 // Output code
EXEC(`ROLLBACK`, []);
Copy

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;
Copy
 // 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*/
   ;
Copy

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:

 -- 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
Copy
Output:
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);
$$;
Copy

DECLARE CURSOR

For now, the Declare Cursor is just being commented.

Source Code

 DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
Copy

Translated Code

 let myCursor1 = new CURSOR(`SELECT COL1 FROM TABLE1`,() => []);
Copy

OPEN

Source Code

 OPEN myCursor1
OPEN GLOBAL myCursor2
Copy

Translated Code

 myCursor1.OPEN();
myCursor2.OPEN()
Copy

FETCH

Source Code

 DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1
Copy

Translated Code

 var VALUE1;
myCursor1.FETCH_NEXT();
VALUE1 = myCursor1.INTO();
Copy

CLOSE

Source Code

 CLOSE myCursor1
CLOSE GLOBAL myCursor2
Copy

Translated Code

 myCursor1.CLOSE()
myCursor2.CLOSE()
Copy

DEALLOCATE

Source Code

 DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
Copy

Translated Code

 myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
Copy

@@FETCH_STATUS

Source Code

 @@FETCH_STATUS
Copy

Translated Code

 myCursor1.FETCH_STATUS()
Copy

@@CURSOR_ROWS

Source Code

 @@CURSOR_ROWS
Copy

Translated Code

 myCursor1.FETCH_STATUS()
Copy

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;
Copy

Translated Code

 VAR1 = 1 + 1;
VAR1 = 1 - 1;
VAR1 = 1 / 1;
VAR1 = 1 * 1;
VAR1 = 1 || 1;
VAR1 = 1 && 1;
Copy

Conditionals

Source Code

 @var1 > 0
@var1 = 0
@var1 < 0
@var1 <> 0
Copy

Translated Code

 VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0
Copy
IN Predicate
NULL Predicate

Source Code

 @var1 is null
@var2 is not null
Copy

Translated Code

 VAR1 == null
VAR2 != null
Copy

5. Labels and Goto

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

 -- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels] 
AS
SUCCESS_EXIT:
	SET @ErrorStatus = 0
	RETURN @ErrorStatus

ERROR_EXIT:
	RETURN @ErrorStatus
Copy
Translated Code
 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;
	}
$$;
Copy

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
 -- 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
Copy
Translated Code
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;
	}
$$;
Copy

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 .

Known Issues

No issues were found.

Related EWIs

  1. SSC-EWI-0040: Statement Not Supported.

  2. SSC-EWI-0073: Pending Functional Equivalence Review.

Tables

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Some parts in the output code are omitted for clarity reasons.

Basic Create Table

Source

 CREATE TABLE [MYSCHEMA].[MYTABLE]
(
    [COL1] INT IDENTITY (1,1) NOT NULL,
    [COL2] INT,
    [COL2 COL3 COL4] VARCHAR,
    [COL VARCHAR_SPANISH] [VARCHAR](20) COLLATE Modern_Spanish_CI_AI DEFAULT 'HOLA',
    [COL VARCHAR_LATIN] [VARCHAR](20) COLLATE Latin1_General_CI_AI DEFAULT 'HELLO' 
);
Copy

Expected

 CREATE OR REPLACE SEQUENCE MYSCHEMA.MYTABLE_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN MYSCHEMA.MYTABLE.COL1';

CREATE OR REPLACE TABLE MYSCHEMA.MYTABLE (
    COL1 INT DEFAULT MYSCHEMA.MYTABLE_COL1.NEXTVAL NOT NULL,
    COL2 INT,
    "COL2 COL3 COL4" VARCHAR,
    "COL VARCHAR_SPANISH" VARCHAR(20) COLLATE 'ES-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HOLA',
    "COL VARCHAR_LATIN" VARCHAR(20) COLLATE 'EN-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HELLO'
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
Copy

Temporary Tables

In the source code, there can be some table names that start with the character #.

 CREATE TABLE #MyLocalTempTable (
        COL1 INT,
        COL2 INT
);
Copy

If that is the case, they are transformed into temporary tables in the output code.

Let’s see how the code from above would be migrated.

 CREATE OR REPLACE TEMPORARY TABLE T_MyLocalTempTable (
        COL1 INT,
        COL2 INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

As you can see, TEMPORARY was added to the definition of the table, and the character # was replaced with T_.

Also, all references of the table will be transformed too, to match the new name given to the temporary table.

NULL and NOT NULL Column Option

NULL and NOT NULL column options are supported in Snowflake.

Source

 CREATE TABLE [SCHEMA1].[TABLE1](
	[COL1] [varchar](20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [SCHEMA1].[TABLE2](
	[COL1] [varchar](20) NULL
) ON [PRIMARY]
GO
Copy

Expected

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
	COL1 VARCHAR(20) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

CREATE OR REPLACE TABLE SCHEMA1.TABLE2 (
	COL1 VARCHAR(20) NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Identity Column Option

For identity columns, a sequence is created and assigned to the column.

Source

 CREATE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT IDENTITY (666, 313) NOT NULL
);
Copy

Expected

 CREATE OR REPLACE SEQUENCE acct3.UnidentifiedCash3_UnidentifiedCash_ID3
START WITH 666
INCREMENT BY 313
COMMENT = 'FOR TABLE-COLUMN acct3.UnidentifiedCash3.UnidentifiedCash_ID3';

CREATE OR REPLACE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT DEFAULT acct3.UnidentifiedCash3_UnidentifiedCash_ID3.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Default Column Option

The default Expr is supported in Snowflake, however, in Sql Server it can come together with a constraint Name. Since that part is not supported in Snowflake, it has been removed, and a warning has been added.

Source

 CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] VARCHAR (10) CONSTRAINT [constraintName] DEFAULT ('0') NOT NULL
);
Copy

Expected

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(10)
                     --** SSC-FDM-0012 - CONSTRAINT IN DEFAULT EXPRESSION IS NOT SUPPORTED IN SNOWFLAKE **
                     DEFAULT ('0') NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Column Constraint

Source

 CREATE TABLE [SalesLT].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) NOT NULL,
	[AddressLine2] [nvarchar](60) NULL,
	[City] [nvarchar](30) NOT NULL,
	[StateProvince] [dbo].[Name] NOT NULL,
	[CountryRegion] [dbo].[Name] NOT NULL,
	[PostalCode] [nvarchar](15) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
	(
		[AddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
	CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED 
	(
		[rowguid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Copy

Expected

 CREATE OR REPLACE SEQUENCE SalesLT.Address_AddressID
	START WITH 1
	INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN SalesLT.Address.AddressID';

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "[dbo].[Name]" **
CREATE OR REPLACE TABLE SalesLT.Address (
	AddressID INT DEFAULT SalesLT.Address_AddressID.NEXTVAL NOT NULL,
	AddressLine1 VARCHAR(60) NOT NULL,
	AddressLine2 VARCHAR(60) NULL,
	City VARCHAR(30) NOT NULL,
	StateProvince VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	CountryRegion VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	PostalCode VARCHAR(15) NOT NULL,
	rowguid VARCHAR
 	               !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 	               ROWGUIDCOL  NOT NULL,
	ModifiedDate TIMESTAMP_NTZ(3) NOT NULL,
	CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID),
	CONSTRAINT AK_Address_rowguid UNIQUE (rowguid)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
;
Copy

Collate Column Option

For the Collate transformation, please check the following link.

collate.md

ENCRYPTED WITH Column Option

The Encrypted With is not supported in Snowflake, so it is being removed, and a warning is added.

Source

 CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] NVARCHAR(60) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);
Copy

Expected

 CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(60)
--                     --** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
--                     ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

NOT FOR REPLICATION

The NOT FOR REPLICATION option is not supported in Snowflake. It is used for the identity that is being migrated to a SEQUENCE.

Warning

Notice that NOT FOR REPLICATION is a statement that is not required in Snowflake because it is translated to an equivalent, so it is removed.

Source

 CREATE TABLE [TABLE1] (
    [COL1] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY];
Copy

Output

 CREATE OR REPLACE SEQUENCE TABLE1_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN TABLE1.COL1';

CREATE OR REPLACE TABLE TABLE1 (
    COL1 INT DEFAULT TABLE1_COL1.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

On Primary

The ON PRIMARY option is a statement that is used in SQL Server to define on which file an object, e.g. a table, is going to be created. Such as on a primary or secondary file group inside the database. Snowflake provides a different logic and indicates distinct constraints. Please review the following Snowflake documentation for more information.

Source

 CREATE TABLE [TABLE1](
[COL1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 CONSTRAINT [pk_dimAddress_AddressId] PRIMARY KEY CLUSTERED ([COL1]) 
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Copy
Output
 CREATE OR REPLACE TABLE TABLE1 (
 COL1 VARCHAR(255) COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ NOT NULL
  CONSTRAINT pk_dimAddress_AddressId PRIMARY KEY (COL1)
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
 ;
Copy

ASC/DESC Column Sorting

Column sorting is not supported in Snowflake, the ASC or DESC keywords are being removed.

Source

 CREATE TABLE [TABLE1](
	[COL1] [int] NOT NULL,
 CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]
Copy
Output
 CREATE OR REPLACE TABLE TABLE1 (
	COL1 INT NOT NULL,
	 CONSTRAINT constraint1 PRIMARY KEY (COL1)
	)
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
	;
Copy

Computed Columns

Computed columns are supported in Snowflake, we just need to add the explicit data type in order to be able to deploy the table, for example.

Source

 CREATE TABLE [TABLE1](
	[COL2] [int] NOT NULL,
	[COL2] [int] NOT NULL,
	[COL1] AS (COL3 * COL2),
)
Copy
Output
 CREATE OR REPLACE TABLE TABLE1 (
	COL2 INT NOT NULL,
	COL2 INT NOT NULL,
	COL1 VARIANT AS (COL3 * COL2) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

If the computed expression cannot transform, a warning is added, and a simple column definition with the expression return type will be used instead, like in the following example:

Source
 CREATE TABLE [TABLE1](
	[Col1] AS (CONVERT ([XML], ExpressionValue))
)
Copy

The expression CONVERT ([NUMERIC], ExpressionValue) is not supported yet by SnowConvert, so, after it is inspected, SnowConvert will determine that its type is XML, so the transformation will be

 CREATE OR REPLACE TABLE TABLE1 (
	Col1 TEXT AS (CAST(ExpressionValue AS VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XML DATA TYPE CONVERTED TO VARIANT ***/!!!)) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

SnowConvert will run a process to determine the original expression type in SQL Server. However, the column will have the equivalent target type. In the previous example, the column type in SQLServer was XML, but the target type in Snowflake for storing an XML is TEXT. For more information about data type mapping, check the data types sections.

MASKED WITH Column Option

In SQL Server the data masking is used to keep sensitive information from nonprivileged users. Review the SQL SERVER documentation for more information. In Snowflake, there is a dynamic data masking functionality but it is available to Enterprise Edition only. Please review the following Snowflake documentation.

Input

 CREATE TABLE TABLE1
(
	[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);
Copy
Output
 CREATE OR REPLACE TABLE TABLE1
(
	COL1 VARCHAR(50)
 	                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'default()') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

ROWGUIDCOL Column Option

ROWGUIDCOL is no longer applicable in Snowflake. It is used in SQL Server for UNIQUEIDENTIFIER types that are currently translated to VARCHAR. For example:

Input

 CREATE TABLE TABLEROWID (
    [ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];
Copy
Output
 CREATE OR REPLACE TABLE TABLEROWID (
    ROWGUID VARCHAR
                    !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                    ROWGUIDCOL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

GENERATED ALWAYS AS ROW START/END Column Option

ROW START/END is not supported in Snowflake. An error is added when SnowConvert try to transform this kind of column option.

Input

 CREATE TABLE TABLEROWID (
    [COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];
Copy
Output
 CREATE OR REPLACE TABLE TABLEROWID (
    COL1 TIMESTAMP_NTZ(3) GENERATED ALWAYS AS ROW START !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GeneratedClause' NODE ***/!!! NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Copy

Known Issues

No issues were found.

Related EWIs

  1. SSC-EWI-0036: Data type converted to another data type.

  2. SSC-EWI-0040: Statement Not Supported.

  3. SSC-EWI-0073: Pending Functional Equivalence Review.

  4. SSC-EWI-TS0017: Masking not supported.

  5. SSC-FDM-0012: Constraint in default expression is not supported.

  6. SSC-FDM-TS0002: This message is shown when there is a collate clause that is not supported in Snowflake.

  7. SSC-FDM-TS0009: Encrypted with not supported in Snowflake.

  8. SSC-FDM-TS0014: Computed column transformed.

  9. SSC-FDM-TS0015: Data type is not supported in Snowflake.

  10. SSC-PRF-0002: Case-insensitive columns can decrease the performance of queries.

Views

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Some parts in the output code are omitted for clarity reasons.

In this section, we will check the transformation for the create view.

Sample Source Patterns

SIMPLE CREATE VIEW

The following example shows a transformation for a simple CREATE VIEW statement.

 CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;
Copy

Expected

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE OR ALTER VIEW

The CREATE OR ALTER definition used in SqlServer is transformed to CREATE OR REPLACE in Snowflake.

 CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable;
Copy
 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW WITH

In this type of View, after the name of the View, the following clauses can come

  • WITH ENCRYPTION

  • WITH SCHEMABINDING

  • WITH VIEW_METADATA

Warning

Notice that the above clauses are removed from the translation. because are not relevant in Snowflake syntax.

 CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS
SELECT AValue from ATable;
Copy
 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW AS SELECT WITH CHECK OPTION

In this type of View, the clause WITH CHECK OPTION comes after the end of the Select statement used in the Create View.

Warning

Notice that WITH CHECK OPTIONis removed from the translation, because is not relevant in Snowflake syntax.

 CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable
WITH CHECK OPTION;
Copy

Expected

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
Copy

CREATE VIEW AS COMMON TABLE EXPRESSION

Common Table Expressions must be used to retrieve the data:

 CREATE VIEW EMPLOYEEIDVIEW
AS
WITH CTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee] 
UNION ALL 
SELECT BusinessEntityID FROM [HumanResources].[EmployeeDepartmentHistory] )
SELECT * FROM MyCTE;
Copy
 CREATE OR REPLACE VIEW EMPLOYEEIDVIEW
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH CTE AS ( SELECT
NationalIDNumber
from
HumanResources.Employee
UNION ALL
SELECT
BusinessEntityID
FROM
HumanResources.EmployeeDepartmentHistory
)
SELECT
*
FROM
MyCTE;
Copy
UNSUPPORTED SCENARIOS

Common table expressions with Update, Insert or Delete statements will be commented out because they are not supported in Snowflake and SQLServer.

In the case where an invalid CTE is added to the view, this will be completely commented out.

 --!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED ***/!!!
--CREATE OR REPLACE VIEW PUBLIC.EmployeeInsertVew
--AS
--WITH MyCTE AS ( SELECT
--NationalIDNumber
--from
--HumanResources.Employee
--UNION ALL
--SELECT
--BusinessEntityID
--FROM
--HumanResources.EmployeeDepartmentHistory
--)
--INSERT INTO PUBLIC.Dummy
Copy

FINAL SAMPLE

Let’s see a final sample, let’s put together all the cases that we have seen so far and see how the transformation would be

 CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS  
Select AValue from ATable
WITH CHECK OPTION;  
Copy

Expected

 CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
Select
AValue
from
ATable;
Copy

As you can see, we changed the OR ALTER with OR REPLACE and we removed the clause WITH ENCRYPTION that comes after the view name and the WITH CHECK OPTION that comes after the Select.

Related EWIs

  1. SSC-PRF-TS0001: Performance warning - recursion for CTE not checked. Might require a recursive keyword.

Azure Synapse Analytics

Applies to
  • [x] Azure Synapse Analytics

Description

This section presents the translation for syntax specific to Azure Synapse Analytics Tables.

Note

For most patterns, check the translation specification for general tables, as they share almost the same syntax and behavior.

Create Table

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( 
      { column_name &#x3C;data_type>  [ &#x3C;column_options> ] } [ ,...n ]
    )  
    [ WITH ( &#x3C;table_option> [ ,...n ] ) ]  
[;]
Copy

Create Table As

 CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  
Copy

Sample Source Patterns

WITH table options

Azure Synapse Analytics presents an additional syntax for defining table options.

 <table_option> ::=
    {
       CLUSTERED COLUMNSTORE INDEX -- default for Azure Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column [,...n])  
      | HEAP --default for Parallel Data Warehouse
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) -- default is ASC
    }  
    {
        DISTRIBUTION = HASH ( distribution_column_name )
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 
      | DISTRIBUTION = ROUND_ROBIN -- default for Azure Synapse Analytics
      | DISTRIBUTION = REPLICATE -- default for Parallel Data Warehouse
    }
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] -- default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) )
Copy

Snowflake automatically handles table optimization through mechanisms like micro-partitioning. For this reason, an equivalent syntax for some of these table options does not exist in Snowflake. Therefore, it is not necessary to define some of Transact’s table options.

Table options that will be omitted:

  • CLUSTERED COLUMNSTORE INDEX (without column)

  • HEAP

  • DISTRIBUTION

  • PARTITION

CLUSTERED [ COLUMNSTORE ] INDEX with columns, will be transformed to Snowflake’s CLUSTER BY. A performance review PRF will be added as it is advised to check if defining a CLUSTER KEY is necessary.

Transact
 CREATE TABLE my_table (
    enterprise_cif INT,
    name NVARCHAR(100),
    address NVARCHAR(255),
    created_at DATETIME
) 
WITH (
    DISTRIBUTION = HASH(enterprise_cif),
    CLUSTERED INDEX (enterprise_cif)
);
Copy
Snowflake
 CREATE OR REPLACE TABLE my_table (
  enterprise_cif INT,
  name VARCHAR(100),
  address VARCHAR(255),
  created_at TIMESTAMP_NTZ(3)
)
--** SSC-PRF-0007 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (enterprise_cif)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/09/2024" }}'
;
Copy

Known Issues

No issues were found.

Related EWIs

  1. SSC-PRF-0007: PERFORMANCE REVIEW - CLUSTER BY.

TEXTIMAGE_ON

Applies to
  • [x] SQL Server

Note

Non-relevant statement.

Warning

Notice that this statement isremoved from the migrationbecause it is a non-relevant syntax. It means that it is not required in Snowflake.

Description

TEXTIMAGE_ON [PRIMARY] is a way in Transact to handle the large information groups inside a table. In Snowflake it is not required to define these kinds of characteristics because Snowflake handles large data files or information in a different arrangement.

Sample Source Patterns

Notice that in this example the TEXTIMAGE_ON [PRIMARY] has been removed due to the unnecessary syntax.

 CREATE TABLE [dbo].[TEST_Person](
	[date_updated] [datetime] NULL
 ) TEXTIMAGE_ON [PRIMARY]
Copy
 CREATE OR REPLACE TABLE dbo.TEST_Person (
	date_updated TIMESTAMP_NTZ(3) NULL
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
 ;
Copy

Known Issues

No issues were found.

Related EWIs

No related EWIs.

FUNCTION

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

SQL Server only supports two types of User Defined Functions:

Using these UDFs types, is possible to subcategorized them into simple and complex, according to the inner logic.

Simple UDFs, matches the SQL Server syntax with Snowflake syntax. This type doesn’t add any logic and goes straightforward to the result. These are usually match to Snowflake’s SQL UDFs.

Complex UDFs, makes extensive use of a particular statements (INSERT, DELETE, UPDATE, SET, DECLARE, etc) or control-of-flow blocks (IF…ELSE, WHILE, etc) and usually represents a mismatch or violation to Snowflake’s SQL UDFs definition.

Limitations

Transact UDFs have some limitations not present in other database engines (such as Oracle and Teradata). These limitations helps the translations by narrowing the failure scope. This means, there are specific scenarios we can expect to avoid.

Here are some of the limitations SQL Server has on UDFs

  • UDFs cannot be used to perform actions that modify the database state

  • User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target

  • User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.

For the full list, please check this link Create User-defined Functions (Database engine)

scalar.md

inline-table-valued.md

INLINE TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

Note

Some parts in the output code are omitted for clarity reasons.

Inline Table-Valued functions are table expression that can accept parameters, perform a SELECT statement and return a TABLE (SQL Server Language Reference Creating an inline table-valued function).

Transact Syntax

 -- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Copy

Snowflake SQL Syntax

 CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'sql
Copy

Sample Source Patterns

The following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.

For Inline Table-Valued functions, there can only exist one statement per body that could be:

  • SELECT Statement

  • WITH Common Table Expression

Select and return values directly from one table

This is the simplest scenario, performing a simple select from a table and returning those values

Transact-SQL
 CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
);

GO

SELECT * from GetDepartmentInfo()
Copy
DepartmentID|Name                      |GroupName                           |
------------+--------------------------+------------------------------------+
           1|Engineering               |Research and Development            |
           2|Tool Design               |Research and Development            |
           3|Sales                     |Sales and Marketing                 |
           4|Marketing                 |Sales and Marketing                 |
           5|Purchasing                |Inventory Management                |
           6|Research and Development  |Research and Development            |
           7|Production                |Manufacturing                       |
           8|Production Control        |Manufacturing                       |
           9|Human Resources           |Executive General and Administration|
          10|Finance                   |Executive General and Administration|
          11|Information Services      |Executive General and Administration|
          12|Document Control          |Quality Assurance                   |
          13|Quality Assurance         |Quality Assurance                   |
          14|Facilities and Maintenance|Executive General and Administration|
          15|Shipping and Receiving    |Inventory Management                |
          16|Executive                 |Executive General and Administration|

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
RETURNS TABLE(
  DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
  GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
    SELECT
    CAST(DepartmentID AS STRING),
    CAST(Name AS STRING),
    CAST(GroupName AS STRING)
    FROM
    HumanResources.Department
$$;

SELECT
    *
from
    TABLE(GetDepartmentInfo());
Copy
DepartmentID|Name                      |GroupName                           |
------------+--------------------------+------------------------------------+
           1|Engineering               |Research and Development            |
           2|Tool Design               |Research and Development            |
           3|Sales                     |Sales and Marketing                 |
           4|Marketing                 |Sales and Marketing                 |
           5|Purchasing                |Inventory Management                |
           6|Research and Development  |Research and Development            |
           7|Production                |Manufacturing                       |
           8|Production Control        |Manufacturing                       |
           9|Human Resources           |Executive General and Administration|
          10|Finance                   |Executive General and Administration|
          11|Information Services      |Executive General and Administration|
          12|Document Control          |Quality Assurance                   |
          13|Quality Assurance         |Quality Assurance                   |
          14|Facilities and Maintenance|Executive General and Administration|
          15|Shipping and Receiving    |Inventory Management                |
          16|Executive                 |Executive General and Administration|

Copy

Select and return values from multiple tables renaming columns and using built in functions

This is an example of a query using built-in functions in a select statement getting data from different tables, renaming columns and returning a table.

Transact-SQL
 CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS 
RETURN
(	
 SELECT TOP (20)
      P.PersonType,
      P.FirstName,
      E.JobTitle,
	  E.Gender,
      YEAR(E.HireDate) as HIREYEAR
  FROM 
      Person.Person P
  INNER JOIN 
      HumanResources.Employee E
  ON
      P.BusinessEntityID = E.BusinessEntityID 
);

GO

SELECT * FROM GetPersonBasicInfo();
Copy
PersonType|FirstName|JobTitle                         |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM        |Ken      |Chief Executive Officer          |M     |    2009|
EM        |Terri    |Vice President of Engineering    |F     |    2008|
EM        |Roberto  |Engineering Manager              |M     |    2007|
EM        |Rob      |Senior Tool Designer             |M     |    2007|
EM        |Gail     |Design Engineer                  |F     |    2008|
EM        |Jossef   |Design Engineer                  |M     |    2008|
EM        |Dylan    |Research and Development Manager |M     |    2009|
EM        |Diane    |Research and Development Engineer|F     |    2008|
EM        |Gigi     |Research and Development Engineer|F     |    2009|
EM        |Michael  |Research and Development Manager |M     |    2009|
EM        |Ovidiu   |Senior Tool Designer             |M     |    2010|
EM        |Thierry  |Tool Designer                    |M     |    2007|
EM        |Janice   |Tool Designer                    |F     |    2010|
EM        |Michael  |Senior Design Engineer           |M     |    2010|
EM        |Sharon   |Design Engineer                  |F     |    2011|
EM        |David    |Marketing Manager                |M     |    2007|
EM        |Kevin    |Marketing Assistant              |M     |    2007|
EM        |John     |Marketing Specialist             |M     |    2011|
EM        |Mary     |Marketing Assistant              |F     |    2011|
EM        |Wanida   |Marketing Assistant              |F     |    2011|    

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
	PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
	 SELECT
	 TOP 20
		CAST(P.PersonType AS STRING),
		CAST(P.FirstName AS STRING),
		CAST(E.JobTitle AS STRING),
		CAST(E.Gender AS STRING),
		YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
	  FROM
		Person.Person P
	  INNER JOIN
			HumanResources.Employee E
	  ON P.BusinessEntityID = E.BusinessEntityID
$$;

SELECT
	 *
FROM
	 TABLE(GetPersonBasicInfo());
Copy
PersonType|FirstName|JobTitle                         |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM        |Ken      |Chief Executive Officer          |M     |    2009|
EM        |Terri    |Vice President of Engineering    |F     |    2008|
EM        |Roberto  |Engineering Manager              |M     |    2007|
EM        |Rob      |Senior Tool Designer             |M     |    2007|
EM        |Gail     |Design Engineer                  |F     |    2008|
EM        |Jossef   |Design Engineer                  |M     |    2008|
EM        |Dylan    |Research and Development Manager |M     |    2009|
EM        |Diane    |Research and Development Engineer|F     |    2008|
EM        |Gigi     |Research and Development Engineer|F     |    2009|
EM        |Michael  |Research and Development Manager |M     |    2009|
EM        |Ovidiu   |Senior Tool Designer             |M     |    2010|
EM        |Thierry  |Tool Designer                    |M     |    2007|
EM        |Janice   |Tool Designer                    |F     |    2010|
EM        |Michael  |Senior Design Engineer           |M     |    2010|
EM        |Sharon   |Design Engineer                  |F     |    2011|
EM        |David    |Marketing Manager                |M     |    2007|
EM        |Kevin    |Marketing Assistant              |M     |    2007|
EM        |John     |Marketing Specialist             |M     |    2011|
EM        |Mary     |Marketing Assistant              |F     |    2011|
EM        |Wanida   |Marketing Assistant              |F     |    2011|

Copy

Select columns using WITH statement

The body of an inline table-valued function can also be specified using a WITH statement as shown below.

Transact-SQL
 CREATE FUNCTION GetMaritalStatusByGender
(
	@P_Gender nchar(1)
)

RETURNS TABLE 
AS
RETURN
(
  WITH CTE AS 
	(
	 SELECT BusinessEntityID, MaritalStatus, Gender 
	 FROM HumanResources.Employee 
	 where Gender = @P_Gender
	)	
  SELECT 
	MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM 
	CTE INNER JOIN Person.Person P
  ON
	CTE.BusinessEntityID = P.BusinessEntityID
);

GO

select * from GetMaritalStatusByGender('F');
Copy
MaritalStatus|Gender|Name                   |
-------------+------+-----------------------+
S            |F     |Terri Duffy            |
M            |F     |Gail Erickson          |
S            |F     |Diane Margheim         |
M            |F     |Gigi Matthew           |
M            |F     |Janice Galvin          |
M            |F     |Sharon Salavaria       |
S            |F     |Mary Dempsey           |
M            |F     |Wanida Benshoof        |
M            |F     |Mary Gibson            |
M            |F     |Jill Williams          |
S            |F     |Jo Brown               |
M            |F     |Britta Simon           |
M            |F     |Margie Shoop           |
M            |F     |Rebecca Laszlo         |
M            |F     |Suchitra Mohan         |
M            |F     |Kim Abercrombie        |
S            |F     |JoLynn Dobney          |
M            |F     |Nancy Anderson         |
M            |F     |Ruth Ellerbrock        |
M            |F     |Doris Hartwig          |
M            |F     |Diane Glimp            |
M            |F     |Bonnie Kearney         |
M            |F     |Denise Smith           |
S            |F     |Diane Tibbott          |
M            |F     |Carole Poland          |
M            |F     |Carol Philips          |
M            |F     |Merav Netz             |
S            |F     |Betsy Stadick          |
S            |F     |Danielle Tiedt         |
S            |F     |Kimberly Zimmerman     |
M            |F     |Elizabeth Keyser       |
M            |F     |Mary Baker             |
M            |F     |Alice Ciccu            |
M            |F     |Linda Moschell         |
S            |F     |Angela Barbariol       |
S            |F     |Kitti Lertpiriyasuwat  |
S            |F     |Susan Eaton            |
S            |F     |Kim Ralls              |
M            |F     |Nicole Holliday        |
S            |F     |Anibal Sousa           |
M            |F     |Samantha Smith         |
S            |F     |Olinda Turner          |
S            |F     |Cynthia Randall        |
M            |F     |Sandra Reátegui Alayo  |
S            |F     |Linda Randall          |
S            |F     |Shelley Dyck           |
S            |F     |Laura Steele           |
S            |F     |Susan Metters          |
S            |F     |Katie McAskill-White   |
M            |F     |Barbara Decker         |
M            |F     |Yvonne McKay           |
S            |F     |Janeth Esteves         |
M            |F     |Brenda Diaz            |
M            |F     |Lorraine Nay           |
M            |F     |Paula Nartker          |
S            |F     |Lori Kane              |
M            |F     |Kathie Flood           |
S            |F     |Belinda Newman         |
M            |F     |Karen Berge            |
M            |F     |Lori Penor             |
M            |F     |Jo Berry               |
M            |F     |Laura Norman           |
M            |F     |Paula Barreto de Mattos|
M            |F     |Mindy Martin           |
M            |F     |Deborah Poe            |
S            |F     |Candy Spoon            |
M            |F     |Barbara Moreland       |
M            |F     |Janet Sheperdigian     |
S            |F     |Wendy Kahn             |
S            |F     |Sheela Word            |
M            |F     |Linda Meisner          |
S            |F     |Erin Hagens            |
M            |F     |Annette Hill           |
S            |F     |Jean Trenary           |
S            |F     |Stephanie Conroy       |
S            |F     |Karen Berg             |
M            |F     |Janaina Bueno          |
M            |F     |Linda Mitchell         |
S            |F     |Jillian Carson         |
S            |F     |Pamela Ansman-Wolfe    |
S            |F     |Lynn Tsoflias          |
M            |F     |Amy Alberts            |
S            |F     |Rachel Valdez          |
M            |F     |Jae Pak                |

Copy
Snowflake SQL
 CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
	MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
	Name VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
	--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
  WITH CTE AS
	(
	 SELECT
	 	BusinessEntityID,
	 	MaritalStatus,
	 	Gender
	 FROM
	 	HumanResources.Employee
	 where
	 	Gender = :P_GENDER
	)
  SELECT
	 CAST(MaritalStatus AS STRING),
	 CAST(Gender AS STRING),
	 CONCAT(P.FirstName,' ', P.LastName) as Name
  FROM
	 CTE
	 INNER JOIN
	 	Person.Person P
  ON CTE.BusinessEntityID = P.BusinessEntityID
$$;

select
  *
from GetMaritalStatusByGender('F') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;
Copy
MaritalStatus|Gender|Name                   |
-------------+------+-----------------------+
S            |F     |Terri Duffy            |
M            |F     |Gail Erickson          |
S            |F     |Diane Margheim         |
M            |F     |Gigi Matthew           |
M            |F     |Janice Galvin          |
M            |F     |Sharon Salavaria       |
S            |F     |Mary Dempsey           |
M            |F     |Wanida Benshoof        |
M            |F     |Mary Gibson            |
M            |F     |Jill Williams          |
S            |F     |Jo Brown               |
M            |F     |Britta Simon           |
M            |F     |Margie Shoop           |
M            |F     |Rebecca Laszlo         |
M            |F     |Suchitra Mohan         |
M            |F     |Kim Abercrombie        |
S            |F     |JoLynn Dobney          |
M            |F     |Nancy Anderson         |
M            |F     |Ruth Ellerbrock        |
M            |F     |Doris Hartwig          |
M            |F     |Diane Glimp            |
M            |F     |Bonnie Kearney         |
M            |F     |Denise Smith           |
S            |F     |Diane Tibbott          |
M            |F     |Carole Poland          |
M            |F     |Carol Philips          |
M            |F     |Merav Netz             |
S            |F     |Betsy Stadick          |
S            |F     |Danielle Tiedt         |
S            |F     |Kimberly Zimmerman     |
M            |F     |Elizabeth Keyser       |
M            |F     |Mary Baker             |
M            |F     |Alice Ciccu            |
M            |F     |Linda Moschell         |
S            |F     |Angela Barbariol       |
S            |F     |Kitti Lertpiriyasuwat  |
S            |F     |Susan Eaton            |
S            |F     |Kim Ralls              |
M            |F     |Nicole Holliday        |
S            |F     |Anibal Sousa           |
M            |F     |Samantha Smith         |
S            |F     |Olinda Turner          |
S            |F     |Cynthia Randall        |
M            |F     |Sandra Reátegui Alayo  |
S            |F     |Linda Randall          |
S            |F     |Shelley Dyck           |
S            |F     |Laura Steele           |
S            |F     |Susan Metters          |
S            |F     |Katie McAskill-White   |
M            |F     |Barbara Decker         |
M            |F     |Yvonne McKay           |
S            |F     |Janeth Esteves         |
M            |F     |Brenda Diaz            |
M            |F     |Lorraine Nay           |
M            |F     |Paula Nartker          |
S            |F     |Lori Kane              |
M            |F     |Kathie Flood           |
S            |F     |Belinda Newman         |
M            |F     |Karen Berge            |
M            |F     |Lori Penor             |
M            |F     |Jo Berry               |
M            |F     |Laura Norman           |
M            |F     |Paula Barreto de Mattos|
M            |F     |Mindy Martin           |
M            |F     |Deborah Poe            |
S            |F     |Candy Spoon            |
M            |F     |Barbara Moreland       |
M            |F     |Janet Sheperdigian     |
S            |F     |Wendy Kahn             |
S            |F     |Sheela Word            |
M            |F     |Linda Meisner          |
S            |F     |Erin Hagens            |
M            |F     |Annette Hill           |
S            |F     |Jean Trenary           |
S            |F     |Stephanie Conroy       |
S            |F     |Karen Berg             |
M            |F     |Janaina Bueno          |
M            |F     |Linda Mitchell         |
S            |F     |Jillian Carson         |
S            |F     |Pamela Ansman-Wolfe    |
S            |F     |Lynn Tsoflias          |
M            |F     |Amy Alberts            |
S            |F     |Rachel Valdez          |
M            |F     |Jae Pak                |

Copy

Known issues

No issues were found

Related EWIs

  1. SSC-FDM-TS0012: Information for the expression was not found. CAST to STRING used

  2. SSC-PRF-TS0001: Performance warning - recursion for CTE not checked. Might require a recursive keyword.

  3. SSC-EWI-0073: Pending Functional Equivalence Review

MULTI-STATEMENT TABLE-VALUED

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Some parts in the output code are omitted for clarity reasons.

Note

All the code samples on this page have not been implemented yet in SnowConvert. They should be interpreted as a reference for how each scenario should be translated to Snowflake. These translations may change in the future.Some parts in the output code are omitted for clarity reasons.

Description

Multi-statement table-valued is similar to Inline-statement table-valued (INLINE TABLE-VALUED. However Multi-statement table-valued may have more than one statement in its function body, the table columns are specified in the return type and it has a BEGIN/END block (SQL Server Language Reference Creating a multi-statement table-valued function

Transact-SQL Syntax

 CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ = default ] [READONLY] }
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]
Copy

Snowflake SQL

 CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'
Copy

Sample Source Patterns

The following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.

The function body of Multi-Statement Table-Valued function must be a SELECT statement. For this reason the others statements must be called separately.

Insert values in a table

Inserts one or more rows into the table and returns the table with the new values

Transact-SQL
 CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
	DECLARE @col varchar(15)
	SET @col = 'Unknown'
	INSERT INTO @behavioral_segments 
	SELECT @col
	
	RETURN 
END

SELECT * FROM calc_behavioral_segment();
Copy
BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
	behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @col varchar(15)
	SET @col = 'Unknown'
	INSERT INTO @behavioral_segments
	SELECT @col

	RETURN
END

SELECT * FROM calc_behavioral_segment();;
Copy
BEHAVIORAL_SEGMENT|
------------------+
           Unknown|

Copy

Insert value according to if/else statement

Inserts a row into the table according to the condition and returns the table with the new value

Transact-SQL
 CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN 
	IF ((@number % 2) = 0)
	BEGIN
		INSERT @numbers SELECT 'Even'
	END
		  
	ELSE
	BEGIN
		INSERT @numbers SELECT 'Odd'
	END

	RETURN
END

SELECT * FROM odd_or_even_number(9);
Copy
       NUMBER_TYPE|
------------------+
               Odd|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
	number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	IF ((@number % 2) = 0)
	BEGIN
		INSERT @numbers SELECT 'Even'
	END

	ELSE
	BEGIN
		INSERT @numbers SELECT 'Odd'
	END

	RETURN
END

SELECT * FROM odd_or_even_number(9);;
Copy
       NUMBER_TYPE|
------------------+
               Odd|

Copy

Inserts multiple according to if/else statement

The example below inserts more than one value into the table and more than one variable is modified according to the condition. Returns the table with the new values

Transact-SQL
 CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15)) 
AS
BEGIN 
	DECLARE @wfh BIT
	DECLARE @team VARCHAR(15)
	DECLARE @computer VARCHAR(15)

	IF @position = 'DEVELOPER'
	BEGIN
		SET @team = 'TEAM_1'
		SET @computer = 'LAPTOP'		
	END

	IF @position = 'IT'
	BEGIN		
		SET @team = 'TEAM_2'
		SET @computer = 'DESKTOP'
	END

	IF @experience = 'JUNIOR'
	BEGIN
		SET @wfh = '0'			
	END
	IF @experience = 'SENIOR'
	BEGIN
		SET @wfh = '1'						
	END

	INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
	RETURN
END

SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');
Copy
   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

Copy
Snowflake
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
	id_employee VARCHAR(50),
	working_from_home BOOLEAN,
	team VARCHAR(15),
	computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @wfh BIT
	DECLARE @team VARCHAR(15)
	DECLARE @computer VARCHAR(15)

	IF @position = 'DEVELOPER'
	BEGIN
		SET @team = 'TEAM_1'
		SET @computer = 'LAPTOP'
	END

	IF @position = 'IT'
	BEGIN
		SET @team = 'TEAM_2'
		SET @computer = 'DESKTOP'
	END

	IF @experience = 'JUNIOR'
	BEGIN
		SET @wfh = '0'
	END
	IF @experience = 'SENIOR'
	BEGIN
		SET @wfh = '1'
	END

	INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
	RETURN
END

SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');;
Copy
   ID_EMPLOYEE|  WORKING_FROM_HOME|     TEAM|   COMPUTER|
--------------+-------------------|---------|-----------|
     123456789|                  1|   TEAM_1|     LAPTOP|

Copy

Warning

In case there are nested if statements and more than one variables are modified in the statements it is necessary to use a stored procedure.

Update values previously inserted

Updates columns values of the table into the function body and returns it with the new values.

Transact-SQL
 CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
	department_name NVARCHAR(50),
	first_name NVARCHAR(50),
	last_name NVARCHAR(50), 
	start_date DATE,
	end_date DATE,
	job_title NVARCHAR(50), 
	months_working INT
)
BEGIN
	INSERT INTO @employee_history
	SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
	LEFT OUTER JOIN employee_department_history EH
		ON D.department_ID = EH.department_ID
	INNER JOIN  Employee E
		ON E.business_entity_ID = EH.business_entity_ID
	INNER JOIN Person P
		ON P.business_entity_ID = E.business_entity_ID 
		

	UPDATE @employee_history
	SET 
		months_working = 
		CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
		ELSE DATEDIFF(MONTH, start_date, end_date)
	END
	RETURN;
END;

SELECT TOP(10) * FROM get_employees_history();
Copy
                      DEPARTMENT_NAME|         FIRST_NAME|          LAST_NAME|      START_DATE|         END_DATE|                           JOB_TITLE|        MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
                                Sales|               Syed|              Abbas|      2013-03-14| 	    NULL|               Pacific Sales Manager|                   106|
                           Production|                Kim|        Abercrombie|      2010-01-16|             NULL|        Production Technician - WC60|                   144|
                    Quality Assurance|              Hazem|           Abolrous|      2009-02-28|             NULL|           Quality Assurance Manager|                   155|
               Shipping and Receiving|              Pilar|           Ackerman|      2009-01-02|             NULL|   Shipping and Receiving Supervisor|                   156|
                           Production|                Jay|              Adams|      2009-03-05|             NULL|        Production Technician - WC60|                   154|
                 Information Services|           François|           Ajenstat|      2009-01-17|             NULL|              Database Administrator|                   156|
                                Sales|                Amy|            Alberts|      2012-04-16|             NULL|              European Sales Manager|                   117|
                           Production|               Greg|           Alderson|      2008-12-02|             NULL|        Production Technician - WC45|                   157|
                    Quality Assurance|               Sean|          Alexander|      2008-12-28|             NULL|        Quality Assurance Technician|                   157|
           Facilities and Maintenance|               Gary|             Altman|      2009-12-02|             NULL|                  Facilities Manager|                   145|


Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY TABLE (
	department_name VARCHAR(50),
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	start_date DATE,
	end_date DATE,
	job_title VARCHAR(50),
	months_working INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
BEGIN
	INSERT INTO @employee_history
	SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
	LEFT OUTER JOIN employee_department_history EH
		ON D.department_ID = EH.department_ID
	INNER JOIN  Employee E
		ON E.business_entity_ID = EH.business_entity_ID
	INNER JOIN Person P
		ON P.business_entity_ID = E.business_entity_ID


	UPDATE @employee_history
	SET
		months_working =
		CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
		ELSE DATEDIFF(MONTH, start_date, end_date)
	END
	RETURN;
END;

SELECT TOP(10) * FROM get_employees_history();;
Copy
                      DEPARTMENT_NAME|         FIRST_NAME|          LAST_NAME|      START_DATE|         END_DATE|                           JOB_TITLE|        MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
                                Sales|               Syed|              Abbas|      2013-03-14| 	    NULL|               Pacific Sales Manager|                   106|
                           Production|                Kim|        Abercrombie|      2010-01-16|             NULL|        Production Technician - WC60|                   144|
                    Quality Assurance|              Hazem|           Abolrous|      2009-02-28|             NULL|           Quality Assurance Manager|                   155|
               Shipping and Receiving|              Pilar|           Ackerman|      2009-01-02|             NULL|   Shipping and Receiving Supervisor|                   156|
                           Production|                Jay|              Adams|      2009-03-05|             NULL|        Production Technician - WC60|                   154|
                 Information Services|           François|           Ajenstat|      2009-01-17|             NULL|              Database Administrator|                   156|
                                Sales|                Amy|            Alberts|      2012-04-16|             NULL|              European Sales Manager|                   117|
                           Production|               Greg|           Alderson|      2008-12-02|             NULL|        Production Technician - WC45|                   157|
                    Quality Assurance|               Sean|          Alexander|      2008-12-28|             NULL|        Quality Assurance Technician|                   157|
           Facilities and Maintenance|               Gary|             Altman|      2009-12-02|             NULL|                  Facilities Manager|                   145|


Copy

Multiple return clauses

In the following sample there is more than one return clause, this is because depending on the situation it is not necessary to keep executing the whole function.

Transact-SQL
CREATE OR ALTER FUNCTION create_new_team(@team_name VARCHAR(50))
RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN 
	DECLARE @employees INT
	SET @employees = (SELECT count(*) FROM employee)
	DECLARE @type VARCHAR(15)
	SET @type = 'small_team'
	IF (@employees &#x3C; 8)
	BEGIN
		INSERT @new_team VALUES (@type, @team_name) 
		RETURN
	END
	
	SET @type = 'big_team'
	INSERT @new_team VALUES (@type, @team_name) 

	RETURN
END

SELECT * FROM create_new_team('Team1');             
Copy
          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|
 

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION create_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
	type VARCHAR(50),
	name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @employees INT
	SET @employees = (SELECT count(*) FROM employee)
	DECLARE @type VARCHAR(15)
	SET @type = 'small_team'
	IF (@employees < 8)
	BEGIN
		INSERT @new_team VALUES (@type, @team_name)
		RETURN
	END

	SET @type = 'big_team'
	INSERT @new_team VALUES (@type, @team_name)

	RETURN
END

SELECT * FROM create_new_team('Team1');;
Copy
          TYPE|           NAME|
--------------+ --------------+
    SMALL_TEAM|          TEAM1|

Copy

Warning

This transformation is applied when there is only one value to insert, if there is more than one value it is necessary to use a stored procedure.

Complex cases

The example is a complex case that uses nested if statements and inserts a value depending on the true condition.

Transact-SQL
 CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN 
	DECLARE @hire_date DATETIME
	SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
	DECLARE @vacation_hours INT
	SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
	DECLARE @time_working INT
	SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))

	IF (@vacation_hours > 0)
	BEGIN
		IF (@time_working > 3)
		BEGIN
			IF (@vacation_hours < 120)
			BEGIN
				INSERT INTO @status VALUES ('Ok')
			END

			IF (@vacation_hours = 120)
			BEGIN
				INSERT INTO @status values ('In the limit')
			END

			IF (@vacation_hours > 120)
			BEGIN
				INSERT INTO @status VALUES ('With excess')
			END
		END
		ELSE
		BEGIN
			INSERT INTO @status values ('Hired recently')
		END
	END
	ELSE
	BEGIN
		INSERT INTO @status values ('No hours')
	END
	RETURN
END

SELECT * FROM vacation_status('adventure-worksken0')
Copy
  VACATION_STATUS|
-----------------+
               OK|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
	vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @hire_date DATETIME
	SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
	DECLARE @vacation_hours INT
	SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
	DECLARE @time_working INT
	SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))

	IF (@vacation_hours > 0)
	BEGIN
		IF (@time_working > 3)
		BEGIN
			IF (@vacation_hours < 120)
			BEGIN
				INSERT INTO @status VALUES ('Ok')
			END

			IF (@vacation_hours = 120)
			BEGIN
				INSERT INTO @status values ('In the limit')
			END

			IF (@vacation_hours > 120)
			BEGIN
				INSERT INTO @status VALUES ('With excess')
			END
		END
		ELSE
		BEGIN
			INSERT INTO @status values ('Hired recently')
		END
	END
	ELSE
	BEGIN
		INSERT INTO @status values ('No hours')
	END
	RETURN
END

SELECT * FROM vacation_status('adventure-worksken0');
Copy
  VACATION_STATUS|
-----------------+
               OK|

Copy

Known Issues

While statements along side queries

The problem with this example is that there’s no way of transforming the while statement to a CTE inside the WITH clause of the main select, this forces us to transform this statement to store procedure to maintain the same logic.

Transact-SQL
--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'  
WHILE @name = 'Another Department'   
BEGIN    
	IF (@department_id &#x3C; 3)
	BEGIN
		SET @name = 'engineering'
	END
	  
	IF @department_id = 3
	BEGIN
		SET @name = 'Tool Design'
	END
	
	SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END

SELECT * FROM get_group_name(9);
Copy
        GROUP_NAME|
------------------+
       Tool Design|

Copy
Snowflake SQL
 !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
	group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
	IF (@department_id < 3)
	BEGIN
		SET @name = 'engineering'
	END

	IF @department_id = 3
	BEGIN
		SET @name = 'Tool Design'
	END

	SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END

SELECT * FROM get_group_name(9);;
Copy
        GROUP_NAME|
------------------+
       Tool Design|

Copy

Declare Cursor

User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure to work with cursors.

Transact-SQL
 --Additional Params: -t JavaScript

CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
	DECLARE @first_specimen VARCHAR(30) ;
	set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
	DECLARE @second_specimen VARCHAR(30);

	DECLARE @specimens TABLE (name_specimen VARCHAR(30))

	DECLARE Cursor1 CURSOR 
	FOR SELECT name_specimen 
	FROM specimen 

	OPEN cursor1
	FETCH NEXT FROM cursor1
	INTO @second_specimen;
	
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		IF @first_specimen <> @second_specimen
		BEGIN
			INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
		END
		FETCH NEXT FROM cursor1
		INTO @second_specimen;
	END

	CLOSE cursor1;
	DEALLOCATE cursor1;

	INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
	RETURN 
END

SELECT * FROM amount_new_specimens(1);
Copy
            AMOUNT|
------------------+
                 3|

Copy
Snowflake SQL
 --Additional Params: -t JavaScript
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!

CREATE OR ALTER FUNCTION amount_new_specimens (ID INT)
RETURNS @new_specimens TABLE (
	amount INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
	DECLARE @first_specimen VARCHAR(30) ;
	set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
	DECLARE @second_specimen VARCHAR(30);

	DECLARE @specimens TABLE (name_specimen VARCHAR(30))

	DECLARE Cursor1 CURSOR
	FOR SELECT name_specimen
	FROM specimen

	OPEN cursor1
	FETCH NEXT FROM cursor1
	INTO @second_specimen;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @first_specimen <> @second_specimen
		BEGIN
			INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
		END
		FETCH NEXT FROM cursor1
		INTO @second_specimen;
	END

	CLOSE cursor1;
	DEALLOCATE cursor1;

	INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
	RETURN
END

SELECT * FROM amount_new_specimens(1);;
Copy
            AMOUNT|
------------------+
                 3|

Copy

Different statements are not supported in Common Tables Expressions

The clauses UPDATE, INSERT, DELETE, ALTER or DROP are not supported on the body of common tables expressions, even after their declaration using a delimitator. For this reason, the function can be modified to work as a stored procedure.

Transact-SQL
 --Additional Params: -t JavaScript

CREATE OR ALTER PROCEDURE product_history 
AS
BEGIN
	DECLARE @product_history TABLE (
		product_name NVARCHAR(50),
		rating INT
	)
	INSERT INTO @product_history
	SELECT P.Name AS product_name, AVG(ALL R.rating) FROM Production.product P
	INNER JOIN  Production.product_review R
		ON R.product_ID = P.product_ID
	GROUP BY P.Name;

	DELETE FROM @product_history
	WHERE rating < 2;

	SELECT * FROM @product_history; 

END
GO;

EXEC product_history
Copy
                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Copy
Snowflake SQL
 CREATE OR REPLACE PROCEDURE product_history ()
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  = '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 = '';
			} 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(`CREATE OR REPLACE TEMPORARY TABLE T_product_history (
   product_name VARCHAR(50),
   rating INT
	)`);
	EXEC(`	INSERT INTO T_product_history
	SELECT
	   P.Name AS product_name,
	   AVG(ALL R.rating) FROM
	   Production.product P
	   INNER JOIN
	      Production.product_review R
	      ON R.product_ID = P.product_ID
	GROUP BY
	   P.Name`);
	EXEC(`DELETE FROM
   T_product_history
   WHERE
   rating < 2`);
	EXEC(`
	SELECT
	   *
	FROM
	   T_product_history`);
$$;

!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;

CALL product_history();
Copy
                      PRODUCT_NAME|            Rating|
----------------------------------+------------------|
                 HL Mountain Pedal|                 3|
            Mountain Bike Socks, M|                 5|
             Road-550-W Yellow, 40|                 5|

Copy

Related EWIs

  1. SSC-EWI-0040: Statement Not Supported.

  2. SSC-EWI-0073: Pending Functional Equivalence Review

SCALAR

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Description

Note

Some parts in the output code are omitted for clarity reasons.

A scalar user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a scalar value. (SQL Server Language Reference CREATE FUNCTION subsection).

Note

These functions are usually used inside the SELECTstatement, or single variable setup (most likely inside a stored procedure).

Transact-SQL Syntax

 -- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]
Copy

Snowflake Syntax

Snowflake allows 3 different languages in their user defined functions:

  • SQL

  • JavaScript

  • Java

For now, SnowConvert will support only SQL and JavaScript as target languages.

Note

SQL user defined functions only supports one query as their body. They can read from the database, but is not allowed to write or modify it. (Scalar SQL UDFs Reference).

 CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
Copy

Note

JavaScript user defined functions allows multiple statements in their bodies, but cannot perform queries to the database. (Scalar JavaScript UDFs Reference)

 CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'
Copy

Sample Source Patterns

Set and Declare Statements

The most common statements in function bodies are the DECLARE and SET statements. For DECLARE statements without default value, the transformation will be ignored. SET statements and DECLARE statements with a default value, will be transformed to a COMMON TABLE EXPRESSION. Each common table expression will contain a column that represents the local variable value.

Transact-SQL
 CREATE OR ALTER FUNCTION PURCHASING.GetVendorName()
RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @result NVARCHAR(50)
	DECLARE @BUSINESSENTITYID INT
	
	SET @BUSINESSENTITYID = 1492
	
	SELECT @result = Name FROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYID
	
	RETURN @result
END

GO

SELECT PURCHASING.GetVendorName() as vendor_name;
Copy
vendor_name            | 
-----------------------+ 
Australia Bike Retailer|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName ()
RETURNS VARCHAR(50)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			1492 AS BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			Name AS RESULT
		FROM
			PURCHASING.VENDOR
		WHERE
			BUSINESSENTITYID = (
				SELECT
					BUSINESSENTITYID
				FROM
					CTE1
			)
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.GetVendorName() as vendor_name;
Copy
VENDOR_NAME            |
-----------------------+
Australia Bike Retailer|

Copy

If/Else Statement Transformation

If/Else statement can be handled in different ways, they can be either transformed to javascript or to SQL using the CASE EXPRESSION inside the select allowing conditionals inside the queries, while the javascript transformation is pretty straightforward, the Case statement might not be so obvious at first glance.

Transact-SQL
 CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
	DECLARE @result VARCHAR(10)
	DECLARE @ActiveFlag BIT
	
	SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
	
	IF @ActiveFlag = 1
		SET @result = 'YES'
	ELSE IF @ActiveFlag = 0 
		SET @result = 'NO'
	
	RETURN @result
END

GO

SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
Copy
has_active_flag|
---------------+
NO             |

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)
RETURNS VARCHAR(10)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(

		SELECT
			ActiveFlag AS ACTIVEFLAG
		from
			PURCHASING.VENDOR v
		where
			v.BUSINESSENTITYID = P_BUSINESSENTITYID
	),
	CTE2 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 1
					THEN 'YES'
				WHEN (
					SELECT
						ACTIVEFLAG
					FROM
						CTE1
				) = 0
					THEN 'NO'
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE2
$$;

SELECT
	PURCHASING.HasActiveFlag(1516) as has_active_flag;
Copy
HAS_ACTIVE_FLAG|
---------------+
NO             |

Copy

Nested Statements

For nested statements, the structured programming is being transformed to a single query. The statements in the control-of-flow are going to be nested in table structures in order to preserve the execution order.

Note

CASE EXPRESSIONS only can return one value per statement

Example

Note

The following code in both programming paradigms is functionally equivalent.

 DECLARE @VendorId AS int;
DECLARE @AccountNumber AS VARCHAR(50);
SELECT @VendorId = poh.VendorID 
    FROM Purchasing.PurchaseOrderHeader poh
    WHERE PurchaseOrderID = 1
SELECT @AccountNumber = v.AccountNumber
    FROM Purchasing.Vendor v
    WHERE v.BusinessEntityID = @VendorId
Copy
 SELECT V.AccountNumber AccountNumber
FROM (SELECT poh.VendorID VendorId 
         FROM Purchasing.PurchaseOrderHeader poh
         WHERE PurchaseOrderID = 1
) T1, Purchasing.Vendor v
WHERE v.BusinessEntityID = T1.VendorId
Copy
AccountNumber|
-------------+
LITWARE0001  |

Copy

Conditional variables through SELECTs

Variable definition and assignment within conditional statements tends to be somewhat problematic, because references to the variable further down the code would have to know where the variable was last modified. Not only that, but if the reference is within another conditional statement, then there would have to be some kind of redirect that references the previous known assignment to the variable.

This is all aggravated by nesting and complex querying that can be found on input code.

In the following scenario, the first IF statement can be transformed without problems, because the contents are straightforward enough. The second and third IF statements are commented out because they’re not supported at the moment, since there are statements other than variable assignments through SELECT.

SQL Server
 CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
    @param1 varchar(12)
)
RETURNS int
AS
BEGIN
    declare @var1 int;
    declare @var2 int;
    declare @var3 int;

    IF @param1 = 'first'
    BEGIN
        select @var1 = col1 + 10 from table1 WHERE id = 0;
        select @var2 = col1 + 20 from table1 WHERE id = 0;
        select @var3 = col1 + 30 from table1 WHERE id = 0;
    END

    IF @param1 = 'second'
    BEGIN
        declare @var4 int = 10;
        select @var1 = col1 + 40 from table1 WHERE id = 0;
        select @var2 = col1 + 40 from table1 WHERE id = 0;
    END

    IF @param1 = 'third'
    BEGIN
        select col1 from table1 where id = 0;
        select @var1 = col1 + 50 from table1 WHERE id = 0;
        select @var2 = col1 + 50 from table1 WHERE id = 0;
    END

    RETURN @var1
END

SELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
Copy
RESULT|
------+
    10|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
    WITH CTE1 AS
    (
        SELECT
            CASE
                WHEN PARAM1 = 'first'
                    THEN (SELECT
                        col1 + 10 AS VAR1 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR1,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 20 AS VAR2 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR2,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 30 AS VAR3 from
                        table1
                        WHERE
                        id = 0)
            END AS VAR3
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE2 AS
    (
        /*    IF @param1 = 'second'
            BEGIN
                declare @var4 int = 10;
                select @var1 = col1 + 40 from table1 WHERE id = 0;
                select @var2 = col1 + 40 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
    CTE3 AS
    (
        /*    IF @param1 = 'third'
            BEGIN
                select col1 from table1 where id = 0;
                select @var1 = col1 + 50 from table1 WHERE id = 0;
                select @var2 = col1 + 50 from table1 WHERE id = 0;
            END*/
        SELECT
            null
    ),
    CTE4 AS
    (

        SELECT
            PURCHASING.SELECTINUDF('first') as result
    )
    SELECT
        VAR1
    FROM
        CTE4
$$ -- Assuming table1.col1 is 0 when ID = 0
;
Copy
RESULT|
------+
    10|

Copy

Assign and return a variable

In this simple pattern, there is a variable declaration, then, that variable is set using a SELECT statement and finally returned. This is going to be migrated to a Common Table Expression in order to keep the original behavior.

SQL Server
 CREATE OR ALTER FUNCTION Purchasing.GetTotalFreight()
RETURNS MONEY AS
BEGIN
	DECLARE @Result MONEY
	SELECT @Result = ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader t
	return @Result
END

GO

select Purchasing.GetTotalFreight() as Result;
Copy
Result      |
------------+
1583978.2263|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			NVL(SUM(t.Freight), 0) AS RESULT from
			Purchasing.PurchaseOrderHeader t
	)
	SELECT
		RESULT
	FROM
		CTE1
$$;

select
	Purchasing.GetTotalFreight() as Result;
Copy
RESULT      |
------------+
1583978.2263|

Copy

Multiple Function Calls

For this specific pattern there are no obvious queries, but there are multiple calls to multiple functions working on the same variable and returning it at the end. Since Snowflake only supports queries inside its functions, the solution for this block is going to be adding it to a Select and nesting the calls inside, making sure the return value is the same as the one on the source.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.Foo
(
	@PARAM1	INT
)
RETURNS varchar(25)
AS
BEGIN
	DECLARE @filter INT = @PARAM1
	DECLARE @NAME VARCHAR(25) = (SELECT Name from Purchasing.Vendor v where BusinessEntityID = @filter) 
	SET @NAME = REPLACE(@NAME, 'Australia', 'USA')
	SET @NAME = REPLACE(@NAME, 'Bike', 'Car')
	RETURN @NAME
END

GO

SELECT PURCHASING.Foo(1492) AS Name;
Copy
Name            |
----------------+
USA Car Retailer|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.Foo (PARAM1 INT)
RETURNS VARCHAR(25)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			PARAM1 AS FILTER
	),
	CTE2 AS
	(
		SELECT
			(SELECT
					Name
				from
					Purchasing.Vendor v
				where
					BusinessEntityID = (
						SELECT
							FILTER
						FROM
							CTE1
					)
			) AS NAME
	),
	CTE3 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE3
			), 'Australia', 'USA') AS NAME
	),
	CTE4 AS
	(
		SELECT
			REPLACE((
				SELECT
					NAME
				FROM
					CTE4
			), 'Bike', 'Car') AS NAME
	)
	SELECT
		NAME
	FROM
		CTE4
$$;

SELECT
	PURCHASING.Foo(1492) AS Name;
Copy
NAME            |
----------------+
USA Car Retailer|

Copy

Increase a variable based on multiple IF conditions and return its value

For this pattern, a variable is modified (increased in this case) using multiple IF conditions. In the beginning, a set of variables is initialized and used to determine whether the result variable should be increased or not. Finally, the result variable is returned.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.FOO() 
RETURNS MONEY
AS
BEGIN
	declare @firstValue MONEY
	declare @secondValue MONEY
	declare @Result MONEY
	select  @Result = 0
	select 	@firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
	select 	@secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
	if @firstValue is not null
		select @Result = @Result + @firstValue
	if @secondValue is not null
		select @Result = @Result + @secondValue
	return @Result 
END

GO

SELECT PURCHASING.Foo() AS Result;
Copy
Result  |
--------+
473.1415|

Copy
Snowflake
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		select
			0 AS RESULT
	),
	CTE2 AS
	(
		select
			SubTotal AS FIRSTVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 1
	),
	CTE3 AS
	(
		select
			SubTotal AS SECONDVALUE
		from
			Purchasing.PurchaseOrderHeader
		where
			PurchaseOrderID = 2
	),
	CTE4 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						FIRSTVALUE
					FROM
						CTE2
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								FIRSTVALUE
							FROM
								CTE2
						) AS RESULT)
			END AS RESULT
	),
	CTE5 AS
	(
		SELECT
			CASE
				WHEN (
					SELECT
						SECONDVALUE
					FROM
						CTE3
				) is not null
					THEN (
					select
						(
							SELECT
								RESULT
							FROM
								CTE1
						) + (
							SELECT
								SECONDVALUE
							FROM
								CTE3
						) AS RESULT)
				ELSE (SELECT
					RESULT
				FROM
					CTE4)
			END AS RESULT
	)
	SELECT
		RESULT
	FROM
		CTE5
$$;

SELECT
	PURCHASING.Foo() AS Result;
Copy
RESULT  |
--------+
473.1415|

Copy

Two or more RETURN statements

For this pattern, the IF block containing the return clause that breaks the code flow is added at the end of the body, like the final statement to be executed in a CASE expression.

Basic Case

For this particular scenario, there is no logic between the conditional RETURN statement and the final RETURN statement, so all body will be mapped to a single CASE EXPRESSION.

SQL Server
 CREATE OR ALTER FUNCTION [PURCHASING].[FOO] ()
RETURNS INT
AS
BEGIN
	IF exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )
		RETURN 1

	RETURN 0
END

GO

SELECT PURCHASING.FOO() as result;
Copy
result|
------+
     1|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	SELECT
		CASE
			WHEN exists (SELECT
					PreferredVendorStatus
				FROM
					Purchasing.Vendor v
			)
				THEN 1
			ELSE 0
		END
$$;

SELECT
	PURCHASING.FOO() as result;
Copy
RESULT|
------+
     1|

Copy

Common Table Expressions

Common table expressions will be kept as in the original code, and they are going to be concatenated with the generated ones. SnowConvert is able to identify first all the original COMMON TABLE EXPRESSION names in order to avoid generating duplicated names.

SQL Server
 CREATE OR ALTER FUNCTION [PURCHASING].[FOO] 
(
	@status INT
) 
Returns INT
As
Begin 
	Declare @result as int = 0

	;WITH ctetable(RevisionNumber) as 
	(
		SELECT RevisionNumber
		FROM Purchasing.PurchaseOrderHeader poh
		where poh.Status = @status
	),
	finalCte As
	(
		SELECT RevisionNumber FROM ctetable	
	)
	
	Select @result = count(RevisionNumber) from finalCte	
	return @result;
End

GO

SELECT PURCHASING.FOO(4) as result;
Copy
result|
------+
  3689|

Copy
Snowflake
 CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS INT)
Returns INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
	WITH CTE1 AS
	(
		SELECT
			0 AS RESULT
	),
	ctetable (
		RevisionNumber
	) as
		(
			SELECT
			RevisionNumber
			FROM
			Purchasing.PurchaseOrderHeader poh
			where
			poh.Status = STATUS
		),
		finalCte As
		(
			SELECT
			RevisionNumber
		FROM
			ctetable
		),
		CTE2 AS
		(
		Select
			COUNT(RevisionNumber) AS RESULT from
			finalCte
		)
		SELECT
		RESULT
		FROM
		CTE2
$$;

SELECT
		PURCHASING.FOO(4) as result;
Copy
RESULT|
------+
  3689|

Copy

Transform to JavaScript UDFs

If there are multiple statements and the function does not access the database in any way, it can be transformed into a JavaScript function keeping the functional equivalence

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.GetFiscalYear 
(
	@DATE AS DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @FiscalYear AS INT
	DECLARE @CurMonth AS INT
	SET @CurMonth = DATEPART(M,@DATE)
	SET @FiscalYear = DATEPART(YYYY, @DATE)
	IF (@CurMonth >= 7)
	BEGIN
		SET @FiscalYear = @FiscalYear + 1 
	END
	RETURN @FiscalYear
END

GO

SELECT PURCHASING.GetFiscalYear('2020-10-10') as DATE;
Copy
 CREATE OR ALTER FUNCTION PURCHASING.[getCleanChargeCode]
(
	@ChargeCode varchar(50)
)
returns varchar(50) as
begin
	declare @CleanChargeCode varchar(50),@Len int,@Pos int=2
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=RIGHT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	set @Pos=LEN(@ChargeCode)-1
	while @Pos > 1
	begin
		set @CleanChargeCode=LEFT(@ChargeCode,@Pos)
		if TRY_CAST(@CleanChargeCode as bigint) is not null
			return @CleanChargeCode
		set @Pos=@Pos-1
	end
	return null
end

GO

SELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
Copy
DATE|
----+
2021|

Copy
CleanChargeCode|
---------------+
16             |

Copy
Snowflake
 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		FISCALYEAR INT;
		CURMONTH INT;
	BEGIN
		 
		 
		CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP);
		FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);
		IF ((:CURMONTH >= 7)) THEN
			BEGIN
				FISCALYEAR := :FISCALYEAR + 1;
			END;
		END IF;
		RETURN :FISCALYEAR;
	END;
$$;


SELECT
	!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GetFiscalYear' NODE ***/!!!
	PURCHASING.GetFiscalYear('2020-10-10') as DATE;
Copy
 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		CLEANCHARGECODE VARCHAR(50);
		LEN INT;
		POS INT := 2;
	BEGIN
		 
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := RIGHT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		POS := LEN(:CHARGECODE)-1;
		WHILE (:POS > 1) LOOP
			CLEANCHARGECODE := LEFT(:CHARGECODE, :POS);
			IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
				RETURN :CLEANCHARGECODE;
			END IF;
			POS := :POS -1;
		END LOOP;
		RETURN null;
	END;
$$;

SELECT
	PURCHASING.getCleanChargeCode('16test') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS CleanChargeCode;
Copy
DATE  |
------+
2021.0|

Copy
CLEANCHARGECODE|
---------------+
16             |

Copy

Known Issues

Warning

User-defined functions cannot be used to perform actions that modify the database state

Warning

User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target

Warning

User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure if you need to use cursors.

Warning

User-defined functions cannot perform control-of-flow statements such as WHILE if there is at least one call to the database

Warning

User-defined functions with references to other user-defined functions that were transformed to Stored Procedures, will be transformed to Stored Procedures too.

Warning

User-defined functions that use @@ROWCOUNT are not supported in SQL and should be transformed to stored procedures in order to keep the functional equivalence.

Warning

User-defined functions that have SELECT statements assigning a variable to itself is not supported in Snowflake. See also SELECT @local_variable

For all the unsupported cases, please check the related EWIs and the patterns below to obtain recommendations and possible workarounds.

Conditionals other than if/else statements along side queries

The next scenario involves the use of the “while statement” along side other queries. The problem with this example is that there’s no way of transforming the while statement to a CTE inside the WITH clause of the main select, this forces us to transform this statement to JavaScript procedure to maintain the same logic.

SQL Server
 CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
    DECLARE @i int = 0, @p int;
    Select @p = COUNT(*) FROM PURCHASING.VENDOR
    
    WHILE (@p < 1000)
    BEGIN
        SET @i = @i + 1
        SET @p = @p + @i
    END
        
    IF (@i = 6)
        RETURN 1
    
    RETURN @p
END

GO

SELECT PURCHASING.FOO() as result;
Copy
result|
------+
  1007|

Copy

Snowflake

 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I INT := 0;
        P INT;
    BEGIN
         
        Select
            COUNT(*)
        INTO
            :P
 FROM
            PURCHASING.VENDOR;
        WHILE (:P < 1000) LOOP
            I := :I + 1;
            P := :P + :I;
        END LOOP;
        IF ((:I = 6)) THEN
            RETURN 1;
        END IF;
        RETURN :P;
    END;
$$;

SELECT
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as result;
Copy
FOO |
----+
1007|

Copy

Assign a variable using its own value iterating through a rowset

In the following example, the variable @names is used to concatenate multiple values from a column into one single string. The variable is updated on each iteration as shown, which is not supported by SnowFlake UDFs. For this scenario, the function should be transformed into a procedure.

SQL Server

 CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @names varchar(8000)
    SET @names = ''
    SELECT @names = ISNULL(@names + ' ', '') + Name from Purchasing.Vendor v 
    return @names              
END

GO

select PURCHASING.FOO() as names;
Copy
names                                                                                                                                                                                                                                                          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 Australia Bike Retailer Allenson Cycles Advanced Bicycles Trikes, Inc. Morgan Bike Accessories Cycling Master Chicago Rent-All Greenwood Athletic Company Compete Enterprises, Inc International Light Speed Training Systems Gardner Touring Cycles Internati|

Copy

Snowflake query

 --** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        NAMES VARCHAR(8000);
    BEGIN
         
        NAMES := '';
        SELECT
            NVL(:NAMES || ' ', 0) + Name
        INTO
            :NAMES
        from
            Purchasing.Vendor v;
        RETURN :NAMES;
    END;
$$;

select
    PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as names;
Copy

Warning

For the described scenarios above, consider the following limitations:

  1. All the calls to user-defined functions in DML queries such as SELECT, INSERT, DELETE, UPDATE or MERGE will fail because calls to Stored Procedures within these queries are not allowed.

  2. Calls to user-defined functions inside procedures, should be preceeded by the CALL keyword.

  3. Use- defined functions used in COMPUTED COLUMNS will fail during the execution.

Related EWIs

  1. SSC-EWI-0067: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.

  2. SSC-EWI-0073: Pending Functional Equivalence Review.

  3. SSC-FDM-0029: User defined function was transformed to a Snowflake procedure.