SSC-FDM-0029

User defined function was transformed to a Snowflake procedure.

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.

This EWI is deprecated, please refer to SSC-EWI-0068 documentation

Severity

Low

Description

Snowflake user defined functions do not support the same features as Oracle or SQL Server. To maintain the functional equivalence the function is transformed to a Snowflake stored procedure. This will affect their usage in queries.

Example Code

SQL Server:

Input Code

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;

Output Code

Oracle:

Input Code

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;

Output Code

Recommendations

• Separate the inside queries to maintain the same logic.

• 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]