SSC-EWI-PG0007

Into clause in 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:

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:

OUT -> PostgreSQL_01.sql
CREATE OR REPLACE PROCEDURE get_max_id (table_name VARCHAR, max_id INTEGER)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "postgresql",  "convertedOn": "07/02/2025",  "domain": "no-domain-provided" }}'
AS $$
DECLARE
    sql_statement VARCHAR;
BEGIN
    sql_statement := 'SELECT MAX(id) FROM ' || table_name || ';';
    EXECUTE IMMEDIATE sql_statement
                                    !!!RESOLVE EWI!!! /*** SSC-EWI-PG0007 - INTO CLAUSE IN DYNAMIC SQL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! INTO max_id;
    RETURN OBJECT_CONSTRUCT('max_id', :max_id);
END;
$$;

Recommendations

Last updated