SSC-EWI-0067

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

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

OUT -> SqlServer_01.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        I INT := 0;
        P INT;
    BEGIN
         
        Select
            COUNT(*)
        INTO
            :P
 FROM
            PURCHASING.VENDOR;
        WHILE (:P < 1000) LOOP
            I := :I + 1;
            P := :P + :I;
        END LOOP;
        IF ((:I = 6)) THEN
            RETURN 1;
        END IF;
        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;

Oracle:

Input Code

IN -> Oracle_01.sql

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;
/

SELECT FUN1(COL2) FROM TABLE1;

Output Code

OUT -> Oracle_01.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE FUN1(PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        VAR1 VARCHAR(20);
        VAR2 VARCHAR(20);
    BEGIN
        SELECT COL1 INTO
            :VAR1
        FROM
            TABLE1
        where col1 = 1;
        VAR2 := NVL(:PAR1 :: STRING, '') || NVL(:VAR1 :: STRING, '');
        RETURN :VAR2;
    END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **

SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! FUN1(COL2) FROM
    TABLE1;

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 [e-mail]