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:
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select 10, 100 into param1, var1;
END;
$$ LANGUAGE plpgsql;
Output Code:
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;
$$;
SELECT INTO with expressions at the right
Input Code:
CREATE OR REPLACE PROCEDURE test_sp1(out param1 int)
AS $$
DECLARE
var1 int;
BEGIN
select into param1, var1 10, 100;
END;
$$ LANGUAGE plpgsql;
Output Code:
Since Snowflake doesn't support this grammar for SELECT INTO, the expressions are moved to the left of the INTO.
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;
$$;
Known Issues
There are no known issues.
Related EWIs.
There are no related EWIs.
Last updated