SSC-EWI-0068

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.

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 OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)
RETURN VARCHAR
IS
    VAR1 VARCHAR(20);
    VAR2 VARCHAR(20);
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1;
    VAR2 := PAR1 || VAR1;
    RETURN VAR2;
END;
/

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]