SCALAR

Translation reference to convert SQL Server UDF (User Defined Functions) with scalar return type to Snowflake.

Description

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

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

SQL Server 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
[ ; ]

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.

SQL

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

JavaScript

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

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.

SQL Server

Query

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;

Result

vendor_name            | 
-----------------------+ 
Australia Bike Retailer|

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName()
RETURNS VARCHAR(50)
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;

Result

VENDOR_NAME            |
-----------------------+
Australia Bike Retailer|

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.

SQL Server

Query

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;

Result

has_active_flag|
---------------+
NO             |

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (
	BusinessEntityID int
)
RETURNS VARCHAR(10)
AS
$$
    WITH
	CTE1 as (SELECT ActiveFlag AS ActiveFlag 
		from PURCHASING.VENDOR v
		where v.BUSINESSENTITYID = 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

Result

HAS_ACTIVE_FLAG|
---------------+
NO             |

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.

CASE EXPRESSIONS only can return one value per statement

Example

The following code in both programming paradigms is functionally equivalent.

Structured Programming

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

SQL

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

Result

AccountNumber|
-------------+
LITWARE0001  |

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. That's why a specific EWI is added when these patterns are found.

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

Query

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

Result

RESULT|
------+
    10|

Snowflake

Query

/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
AS
$$
    WITH
    --** MSC-ERROR - MSCEWI4053 - ONE OR MORE SELECT STATEMENTS INSIDE THIS CONDITIONAL STATEMENT WERE PARTIALLY TRANSFORMED. FUNCTIONAL EQUIVALENCE CANNOT BE GUARANTEED **
    CTE1 AS
    (
        SELECT
            CASE
                WHEN PARAM1 = 'first'
                    THEN (SELECT
                        col1 + 10 AS VAR1 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR1,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 20 AS VAR2 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR2,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 30 AS VAR3 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR3
    ),
    CTE2 AS
    (
        /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR IF STATEMENT IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
        /*    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
    ),
    CTE3 AS
    (
        /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR IF STATEMENT IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
        /*    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
    )
    SELECT
        VAR1
    FROM
        CTE3
$$;

SELECT PURCHASING.SELECTINUDF('first') as result;

Result

RESULT|
------+
    10|

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

Query

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;

Result

Result      |
------------+
1583978.2263|

Snowflake

Query

CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight()
RETURNS DOUBLE 
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;

Result

RESULT      |
------------+
1583978.2263|

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

Query

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;

Result

Name            |
----------------+
USA Car Retailer|

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.Foo
(
	PARAM1 INT
)
RETURNS varchar(25)
AS
$$
	WITH CTE1 AS (SELECT PARAM1 AS filter),
		CTE2 AS (SELECT NAME AS "NAME" from Purchasing.Vendor v where BusinessEntityID = (SELECT filter FROM CTE1)),
		CTE3 AS (SELECT REPLACE((SELECT "NAME" FROM CTE2), 'Australia', 'USA') AS "NAME"),
		CTE4 AS (SELECT REPLACE((SELECT "NAME" FROM CTE3), 'Bike', 'Car') AS "NAME")
	SELECT "NAME" FROM CTE4
$$;

SELECT PURCHASING.Foo(1492) AS Name;

Result

NAME            |
----------------+
USA Car Retailer|

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

Query

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;

Result

Result  |
--------+
473.1415|

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.FOO() 
RETURNS DOUBLE
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 RESULT FROM CTE1) + (SELECT firstValue FROM CTE2) ELSE (SELECT RESULT FROM CTE1) END AS RESULT),
   		CTE5 AS (SELECT CASE WHEN ((SELECT secondValue FROM CTE3) IS NOT NULL) THEN (SELECT RESULT FROM CTE4) + (SELECT secondValue FROM CTE3) ELSE (SELECT RESULT FROM CTE4) END AS RESULT) 
	SELECT RESULT FROM CTE5
$$;

SELECT PURCHASING.Foo() as Result;

Result

RESULT  |
--------+
473.1415|

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

Query

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;

Result

result|
------+
     1|

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
Returns INT
AS 
$$
   SELECT CASE
              WHEN exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v)
	      THEN 1
	      ELSE 0
	   END
$$;

SELECT PURCHASING.FOO() as result;

Result

RESULT|
------+
     1|

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

Query

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;

Result

result|
------+
  3689|

Snowflake

Query

CREATE OR REPLACE FUNCTION PURCHASING.FOO
(
	status INT
)
Returns INT
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) result from finalCte	
	)
	SELECT RESULT FROM cte2
$$;

SELECT PURCHASING.FOO(4) as result

Result

RESULT|
------+
  3689|

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

Query 1

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;

Query 2

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;

Result 1

DATE|
----+
2021|

Result 2

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

Snowflake

Query 1

CREATE OR REPLACE FUNCTION PURCHASING.GetFiscalYear (DATE date) 
RETURNS INT
LANGUAGE javascript
as
$$
    let FISCALYEAR;
    let CURMONTH;
    
    CURMONTH = DATE.getMonth();
    FISCALYEAR = DATE.getFullYear();
    
    if(CURMONTH >= 7)
    {
        FISCALYEAR = FISCALYEAR + 1;
    }
    return FISCALYEAR;
$$;

SELECT PURCHASING.GetFiscalYear('2020-10-10') AS DATE;

Query 2

CREATE OR REPLACE FUNCTION PURCHASING.getCleanChargeCode(CHARGECODE string)
RETURNS string
LANGUAGE Javascript
AS
$$
  let CleanChargeCode, Len, Pos = 2;
  pos = CHARGECODE.LENGTH - 1;
  var cleanChargedCode;
  while (pos > 1){
    cleanChargedCode = CHARGECODE.slice(-pos)
    if(Number(cleanChargedCode)) {
      return cleanChargedCode
    }
    pos = pos-1;
  }
  pos = CHARGECODE.length-1
  while(pos > 1) {
    cleanChargedCode = CHARGECODE.slice(0, pos)
    if(Number(cleanChargedCode)){
      return cleanChargedCode
    }
    pos = pos-1;
  }
  return null
$$;

SELECT PURCHASING.getCleanChargeCode('16test') AS CleanChargeCode;

Result 1

DATE  |
------+
2021.0|

Result 2

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

Known Issues

User-defined functions 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 DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure if you need to use cursors.

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

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

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.

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

Query

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;

Result

result|
------+
  1007|

Snowflake

Query

CREATE OR REPLACE PROCEDURE PURCHASING.FOO()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
   var SELECT = (sql,binds = [],...args) => {
      var reducers = args.filter((i) => i instanceof Function);
      reducers = reducers.length ? reducers : [(value) => value]
      args = args.splice(0,args.length - reducers.length)
      EXEC("SELECT " + sql,binds)
      if (ROW_COUNT < 1) return;
      var colCount = _ROWS.getColumnCount();
      if (colCount != reducers.length) throw new Error("Missing arguments results has ${colCount} columns");
      var cols = Array.from(Array(colCount),() => []);
      while ( _ROWS.next() ) {
         for(var i = 0;i < colCount;i++) {
            cols[i].push(_ROWS.getColumnValue(i + 1))
         }
      }
      if (colCount == 1) {
         cols[0].forEach((value) => reducers[0](value))
         return (cols[0])[0];
      }
      for(var i = 0;i < colCount;i++) {
         cols[i].forEach((value) => reducers[i](value))
      }
   };
   var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', 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

   let I = 0;
   let P;
   SELECT(`COUNT(*) FROM PURCHASING.VENDOR`,[],(value) => P = value);
   while ( P < 1000 ) {
      I = I + 1;
      P = P + I;
   }
   if (I == 6) {
      return 1;
   }
   return P;
$$;

CALL PURCHASING.FOO();

Result

FOO |
----+
1007|

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

Query

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;

Result

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|

Snowflake query

CREATE OR REPLACE PROCEDURE PURCHASING.FOO()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
      // SOME IMPLEMENTATIONS
   // END REGION

   let NAMES;
   NAMES = ``;
   // ** MSC-INFORMATION - MSCINF0016 - SELECTION OF @names MAY HAVE RUNTIME ERRORS **
   SELECT(`NVL(? + ' ', '') /*** MSC-WARNING - MSCEWI1046 - 'ISNULL' FUNCTION MAPPED TO 'NVL', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ + Name from Purchasing.Vendor v`,[NAMES],(value) => NAMES = value);
   return NAMES;
$$;

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. MSCEWI1067: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.

2. MSCEWI1068: User-defined function was transformed to a Snowflake procedure.

3. MSCEWI1069: User-defined function inner logic was transformed to a single Common Table Expression.

4. MSCEWI1070: User-defined function body not generated.

5. MSCEWI1071: User-defined function structured block was transformed to a single SQL Query.

6. MSCEWI4028: Labels inside user-defined function were removed.