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:
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
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;
$$;
