SSC-EWI-PG0007

Into clause on Dynamic SQL is not support in Snowflake

Severity

Low

Description

PostgreSQL Dynamic SQL allows the INTO clause to store query results in variables. Snowflake does not support this functionality. Therefore, the INTO clause will be flagged with an EWI'.

Code Example

Input Code:

PosgreSQL

IN -> PostgreSQL_01.sql

CREATE OR REPLACE PROCEDURE get_max_id(table_name VARCHAR, OUT max_id INTEGER)
AS $$
DECLARE
    sql_statement VARCHAR;
BEGIN
    sql_statement := 'SELECT MAX(id) FROM ' || table_name || ';';
    EXECUTE sql_statement INTO max_id;
END;
$$ LANGUAGE plpgsql;

Output Code:

Snowflake

OUT -> PostgreSQL_01.sql

CREATE OR REPLACE PROCEDURE get_max_id (table_name VARCHAR, max_id INTEGER)
RETURNS VARIANT
LANGUAGE SQL
AS $$
        DECLARE
            sql_statement VARCHAR;
BEGIN
    sql_statement := 'SELECT MAX(id) FROM
   ' || table_name;
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
            EXECUTE IMMEDIATE sql_statement
                                            !!!RESOLVE EWI!!! /*** SSC-EWI-PG0007 - INTO CLAUSE ON DYNAMIC SQL IS NOT SUPPORT IN SNOWFLAKE. ***/!!! INTO max_id;
            RETURN OBJECT_CONSTRUCT('max_id', :max_id);
END;
$$;

Recommendations

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