SSC-EWI-0067

UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

High

Description

This error is added when a call to a UDF (user defined function) is found inside a query. Oracle UDFs and UDFs inside packages and some SQL Server UDFs, are being transformed to Snowflake Stored Procedures, which can not be called from a query.

The function is transformed to a Stored procedure to maintain functional equivalence and the function call is transformed to an empty Snowflake UDF function.

This EWI is strongly related to SSC-EWI-0068

Example Code

SQL Server:

Input Code

IN -> SqlServer_01.sql

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;

Output Code

Oracle:

Input Code

IN -> Oracle_01.sql

CREATE FUNCTION employee_function (param1 in NUMBER) RETURN NUMBER is
  var1    employees.employee_ID%TYPE;
  var2    employees.manager_ID%TYPE;
  var3    employees.title%TYPE;
BEGIN
  SELECT employee_ID, manager_ID, title
  INTO var1, var2, var3
  FROM employees
    START WITH manager_ID = param1
    CONNECT BY manager_ID = PRIOR employee_id;
  RETURN var1;
EXCEPTION
   WHEN no_data_found THEN RETURN param1;
END employee_function;

SELECT employee_function(2) FROM employees;

Output Code

Recommendations

• The source code may need to be restructured to fit with the Snowflake user-defined functions approach.

• If you need more support, you can email us at [email protected]