Local Variables and Parameters

Description

Redshift also allow to SELECT INTO variables when the statement is executed inside stored procedures.

This pattern is fully supported in Snowflake.

Grammar Syntax

SELECT [ select_expressions ] INTO target [ select_expressions ] FROM ...;

Sample Source Patterns

SELECT INTO with expressions at the left

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;

Results

param1
10

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select 10, 100 into
                : param1,
                : var1;
            RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;

Results

TEST_SP1
{ "param1": 10 }

SELECT INTO with expressions at the right

Input Code:

Redshift

IN -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
    var1 int;
BEGIN
     select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;

Results

param1
10

Output Code:

Since Snowflake doesn't support this grammar for SELECT INTO, the expressions are moved to the left of the INTO.

Snowflake

OUT -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE test_sp1 (param1 int)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            var1 int;
BEGIN
     select
                10, 100
            into
                : param1,
                : var1;
            RETURN OBJECT_CONSTRUCT('param1', :param1);
END;
$$;

Results

TEST_SP1
{ "param1": 10 }

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.