EXECUTE

Description

The EXECUTE IMMEDIATE statement builds and runs a dynamic SQL statement in a single operation.

Native dynamic SQL uses the EXECUTE IMMEDIATE statement to process most dynamic SQL statements. (Redshift Language Reference EXECUTE Statement)

Grammar Syntax

EXECUTE command-string [ INTO target ];

Sample Source Patterns

Concated Example

Input Code

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE create_dynamic_table(table_name VARCHAR) 
AS $$ 
DECLARE 
sql_statement VARCHAR; 
BEGIN 
sql_statement := 'CREATE TABLE IF NOT EXISTS ' || table_name || ' (id INT, value VARCHAR);'; 
EXECUTE sql_statement; 
END;
$$ LANGUAGE plpgsql; 

Output Code

Snowflake

OUT ->Redshift_01.sql

CREATE OR REPLACE PROCEDURE create_dynamic_table (table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS $$
        DECLARE
            sql_statement VARCHAR;
BEGIN
sql_statement := 'CREATE TABLE IF NOT EXISTS ' || table_name || ' (id INT, value VARCHAR)';
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE sql_statement;
END;
$$;

Function Transformation

Input Code

Redshift

IN -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE insert_with_dynamic()
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'insert into orders(order_date) values ("getdate"());';
EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;

Output Code

Snowflake

OUT -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE insert_with_dynamic ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS $$
        DECLARE
            sql_statement VARCHAR;
BEGIN
sql_statement := 'insert into orders (order_date) values (GETDATE())';
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE sql_statement;
END;
$$;

Error In Query Parsing

Input Code

Redshift

IN -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE bad_statement(table_name VARCHAR) 
AS $$ 
DECLARE 
sql_statement VARCHAR; 
BEGIN 
sql_statement := 'bad statement goes here'; 
EXECUTE sql_statement; 
END;
$$ LANGUAGE plpgsql;

Output Code

Snowflake

OUT -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE bad_statement (table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS $$
        DECLARE
            sql_statement VARCHAR;
BEGIN
sql_statement := 'bad statement goes here';
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!
EXECUTE sql_statement;
END;
$$;

INTO Clause

Input Code

Redshift

IN -> Redshift_04.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 -> Redshift_04.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": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
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 sql_statement
                          !!!RESOLVE EWI!!! /*** SSC-EWI-0021 - EXECUTE IMMEDIATE RETURNING CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!! INTO max_id;
            RETURN OBJECT_CONSTRUCT('max_id', :max_id);
END;
$$;

Known Issues

1. Execution results cannot be stored in variables.

SnowScripting does not support INTO nor BULK COLLECT INTO clauses. For this reason, results will need to be passed through other means.

2. Dynamic SQL Execution queries may be marked incorrectly as non-runnable.

In some scenarios there an execute statement may be commented regardless of being safe or non-safe to run so please take this into account:

Related EWIs

1. SSC-EWI-0027: Variable with invalid query.

2. SSC-EWI-0030: The statement below has usages of dynamic SQL.