Translation reference to convert SQL Server UDF (User Defined Functions) with scalar return type to Snowflake.
Description
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).
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 SyntaxCREATE [ 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_bodyRETURN scalar_expressionEND[ ; ]
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 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).
JavaScript user defined functions allows multiple statements in their bodies, but cannot perform queries to the database. (Scalar JavaScript UDFs Reference)
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
IN -> SqlServer_01.sql
CREATEORALTERFUNCTION PURCHASING.GetVendorName()RETURNSNVARCHAR(50) ASBEGINDECLARE @result NVARCHAR(50)DECLARE @BUSINESSENTITYID INTSET @BUSINESSENTITYID =1492SELECT @result =NameFROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYIDRETURN @resultENDGOSELECT PURCHASING.GetVendorName() as vendor_name;
vendor_name |
-----------------------+
Australia Bike Retailer|
Snowflake
OUT -> SqlServer_01.sql
CREATE OR REPLACEFUNCTIONPURCHASING.GetVendorName ()RETURNSVARCHAR(50)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (SELECT1492AS BUSINESSENTITYID ), CTE2 AS (SELECTNameAS RESULTFROM PURCHASING.VENDORWHERE BUSINESSENTITYID = (SELECT BUSINESSENTITYIDFROM CTE1 ) )SELECT RESULTFROM CTE2$$;SELECT PURCHASING.GetVendorName() as vendor_name;
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
IN -> SqlServer_02.sql
CREATEORALTERFUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)RETURNSVARCHAR(10) ASBEGINDECLARE @result VARCHAR(10)DECLARE @ActiveFlag BITSELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityIDIF @ActiveFlag =1SET @result ='YES'ELSEIF @ActiveFlag =0SET @result ='NO'RETURN @resultENDGOSELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
has_active_flag|
---------------+
NO |
Snowflake
OUT -> SqlServer_02.sql
CREATE OR REPLACEFUNCTIONPURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)RETURNSVARCHAR(10)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (SELECT ActiveFlag AS ACTIVEFLAGfrom PURCHASING.VENDOR vwhere v.BUSINESSENTITYID = P_BUSINESSENTITYID ), CTE2 AS (SELECTCASEWHEN (SELECT ACTIVEFLAGFROM CTE1 ) =1THEN'YES'WHEN (SELECT ACTIVEFLAGFROM CTE1 ) =0THEN'NO'ENDAS RESULT )SELECT RESULTFROM CTE2$$;SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
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.
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
IN -> SqlServer_03.sql
CREATEorALTERFUNCTION PURCHASING.SELECTINUDF ( @param1 varchar(12))RETURNSintASBEGINdeclare @var1 int;declare @var2 int;declare @var3 int;IF @param1 ='first'BEGINselect @var1 = col1 +10from table1 WHERE id =0;select @var2 = col1 +20from table1 WHERE id =0;select @var3 = col1 +30from table1 WHERE id =0;ENDIF @param1 ='second'BEGINdeclare @var4 int=10;select @var1 = col1 +40from table1 WHERE id =0;select @var2 = col1 +40from table1 WHERE id =0;ENDIF @param1 ='third'BEGINselect col1 from table1 where id =0;select @var1 = col1 +50from table1 WHERE id =0;select @var2 = col1 +50from table1 WHERE id =0;ENDRETURN @var1ENDSELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
RESULT|
------+
10|
Snowflake
OUT -> SqlServer_03.sql
CREATE OR REPLACEFUNCTIONPURCHASING.SELECTINUDF (PARAM1 STRING)RETURNSINTLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (SELECTCASEWHEN PARAM1 ='first'THEN (SELECT col1 +10AS VAR1 from table1WHERE id =0)ENDAS VAR1,CASEWHEN PARAM1 ='first'THEN (SELECT col1 +20AS VAR2 from table1WHERE id =0)ENDAS VAR2,CASEWHEN PARAM1 ='first'THEN (SELECT col1 +30AS VAR3 from table1WHERE id =0)ENDAS 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*/SELECTnull ), !!!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*/SELECTnull ), CTE4 AS (SELECT PURCHASING.SELECTINUDF('first') as result )SELECT VAR1FROM CTE4$$ -- Assuming table1.col1 is 0 when ID = 0;
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
IN -> SqlServer_04.sql
CREATEORALTERFUNCTION Purchasing.GetTotalFreight()RETURNSMONEYASBEGINDECLARE @Result MONEYSELECT @Result =ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader treturn @ResultENDGOselect Purchasing.GetTotalFreight() as Result;
Result |
------------+
1583978.2263|
Snowflake
OUT -> SqlServer_04.sql
CREATE OR REPLACEFUNCTIONPurchasing.GetTotalFreight ()RETURNSNUMBER(38, 4)LANGUAGESQLCOMMENT ='{"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 RESULTFROM CTE1$$;select Purchasing.GetTotalFreight() as 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
IN -> SqlServer_05.sql
CREATEORALTERFUNCTION PURCHASING.Foo( @PARAM1 INT)RETURNSvarchar(25)ASBEGINDECLARE @filter INT= @PARAM1DECLARE @NAME VARCHAR(25) = (SELECTNamefrom Purchasing.Vendor v where BusinessEntityID = @filter) SET @NAME =REPLACE(@NAME, 'Australia', 'USA')SET @NAME =REPLACE(@NAME, 'Bike', 'Car')RETURN @NAMEENDGOSELECT PURCHASING.Foo(1492) ASName;
Name |
----------------+
USA Car Retailer|
Snowflake
OUT -> SqlServer_05.sql
CREATE OR REPLACEFUNCTIONPURCHASING.Foo (PARAM1 INT)RETURNSVARCHAR(25)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (SELECT PARAM1 ASFILTER ), CTE2 AS (SELECT (SELECTNamefrom Purchasing.Vendor vwhere BusinessEntityID = (SELECTFILTERFROM CTE1 ) ) ASNAME ), CTE3 AS (SELECTREPLACE((SELECTNAMEFROM CTE3 ), 'Australia', 'USA') ASNAME ), CTE4 AS (SELECTREPLACE((SELECTNAMEFROM CTE4 ), 'Bike', 'Car') ASNAME )SELECTNAMEFROM CTE4$$;SELECT PURCHASING.Foo(1492) ASName;
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
IN -> SqlServer_06.sql
CREATEORALTERFUNCTION PURCHASING.FOO() RETURNSMONEYASBEGINdeclare @firstValue MONEYdeclare @secondValue MONEYdeclare @Result MONEYselect @Result =0select @firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID =1select @secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID =2if @firstValue is not nullselect @Result = @Result + @firstValueif @secondValue is not nullselect @Result = @Result + @secondValuereturn @Result ENDGOSELECT PURCHASING.Foo() AS Result;
Result |
--------+
473.1415|
Snowflake
OUT -> SqlServer_06.sql
CREATE OR REPLACEFUNCTIONPURCHASING.FOO ()RETURNSNUMBER(38, 4)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (select0AS RESULT ), CTE2 AS (select SubTotal AS FIRSTVALUEfrom Purchasing.PurchaseOrderHeaderwhere PurchaseOrderID =1 ), CTE3 AS (select SubTotal AS SECONDVALUEfrom Purchasing.PurchaseOrderHeaderwhere PurchaseOrderID =2 ), CTE4 AS (SELECTCASEWHEN (SELECT FIRSTVALUEFROM CTE2 ) is not nullTHEN (select (SELECT RESULTFROM CTE1 ) + (SELECT FIRSTVALUEFROM CTE2 ) AS RESULT)ENDAS RESULT ), CTE5 AS (SELECTCASEWHEN (SELECT SECONDVALUEFROM CTE3 ) is not nullTHEN (select (SELECT RESULTFROM CTE1 ) + (SELECT SECONDVALUEFROM CTE3 ) AS RESULT)ELSE (SELECT RESULTFROM CTE4)ENDAS RESULT )SELECT RESULTFROM CTE5$$;SELECT PURCHASING.Foo() AS 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
IN -> SqlServer_07.sql
CREATEORALTERFUNCTION [PURCHASING].[FOO] ()RETURNSINTASBEGINIFexists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )RETURN1RETURN0ENDGOSELECT PURCHASING.FOO() as result;
result|
------+
1|
Snowflake
OUT -> SqlServer_07.sql
CREATE OR REPLACEFUNCTIONPURCHASING.FOO ()RETURNSINTLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$SELECTCASEWHENexists (SELECT PreferredVendorStatusFROM Purchasing.Vendor v )THEN1ELSE0END$$;SELECT PURCHASING.FOO() as 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
IN -> SqlServer_08.sql
CREATEORALTERFUNCTION [PURCHASING].[FOO] ( @status INT) ReturnsINTAsBeginDeclare @result asint=0 ;WITH ctetable(RevisionNumber) as (SELECT RevisionNumberFROM Purchasing.PurchaseOrderHeader pohwhere poh.Status = @status ), finalCte As (SELECT RevisionNumber FROM ctetable )Select @result =count(RevisionNumber) from finalCte return @result;EndGOSELECT PURCHASING.FOO(4) as result;
result|
------+
3689|
Snowflake
OUT -> SqlServer_08.sql
CREATE OR REPLACEFUNCTIONPURCHASING.FOO (STATUSINT)ReturnsINTLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'AS$$WITH CTE1 AS (SELECT0AS RESULT ), ctetable ( RevisionNumber ) as (SELECT RevisionNumberFROM Purchasing.PurchaseOrderHeader pohwhere poh.Status =STATUS ), finalCte As (SELECT RevisionNumberFROM ctetable ), CTE2 AS (SelectCOUNT(RevisionNumber) AS RESULT from finalCte )SELECT RESULTFROM CTE2$$;SELECT PURCHASING.FOO(4) as 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
CREATEORALTERFUNCTION PURCHASING.[getCleanChargeCode]( @ChargeCode varchar(50))returnsvarchar(50) asbegindeclare @CleanChargeCode varchar(50),@Len int,@Pos int=2set @Pos=LEN(@ChargeCode)-1while @Pos >1beginset @CleanChargeCode=RIGHT(@ChargeCode,@Pos)ifTRY_CAST(@CleanChargeCode asbigint) is not nullreturn @CleanChargeCodeset @Pos=@Pos-1endset @Pos=LEN(@ChargeCode)-1while @Pos >1beginset @CleanChargeCode=LEFT(@ChargeCode,@Pos)ifTRY_CAST(@CleanChargeCode asbigint) is not nullreturn @CleanChargeCodeset @Pos=@Pos-1endreturnnullendGOSELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
DATE|
----+
2021|
CleanChargeCode|
---------------+
16 |
Snowflake
OUT -> SqlServer_09.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLARE FISCALYEAR INT; CURMONTH INT;BEGIN CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP); FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);IF ((:CURMONTH >=7)) THENBEGIN FISCALYEAR := :FISCALYEAR +1;END;ENDIF;RETURN :FISCALYEAR;END;$$;SELECT !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GetFiscalYear' NODE ***/!!! PURCHASING.GetFiscalYear('2020-10-10') asDATE;
OUT -> SqlServer_10.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$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 asBIGINT) is not null) THENRETURN :CLEANCHARGECODE;ENDIF; POS := :POS -1;ENDLOOP; POS :=LEN(:CHARGECODE)-1;WHILE (:POS >1) LOOP CLEANCHARGECODE :=LEFT(:CHARGECODE, :POS);IF (TRY_CAST(:CLEANCHARGECODE asBIGINT) is not null) THENRETURN :CLEANCHARGECODE;ENDIF; POS := :POS -1;ENDLOOP;RETURNnull;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;
DATE |
------+
2021.0|
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.
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE PURCHASING.FOO ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLARE I INT :=0; P INT;BEGINSelectCOUNT(*)INTO :PFROM PURCHASING.VENDOR;WHILE (:P <1000) LOOP I := :I +1; P := :P + :I;ENDLOOP;IF ((:I =6)) THENRETURN1;ENDIF;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;
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
IN -> SqlServer_12.sql
CREATEORALTERFUNCTION PURCHASING.FOO()RETURNSVARCHAR(8000)ASBEGINDECLARE @names varchar(8000)SET @names =''SELECT @names =ISNULL(@names +' ', '') +Namefrom Purchasing.Vendor v return @names ENDGOselect PURCHASING.FOO() as names;
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
OUT -> SqlServer_12.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE PURCHASING.FOO ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTEASCALLERAS$$DECLARE NAMES VARCHAR(8000);BEGIN NAMES :='';SELECT NVL(:NAMES ||' ', 0) +NameINTO :NAMESfrom 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;
For the described scenarios above, consider the following limitations:
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.
Calls to user-defined functions inside procedures, should be preceeded by the CALL keyword.
Use- defined functions used in COMPUTED COLUMNS will fail during the execution.
Related EWIs
SSC-EWI-0067: UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.