ARGUMENTS MODE

Description

Amazon Redshift stored procedures support parameters that can be passed during procedure invocation. These parameters allow you to provide input values, retrieve output values, or use them for input and output operations. Below is a detailed explanation of the types of parameters, their modes, and examples of their usage. Snowflake only supports input values.

IN (Input Parameters)

Purpose: Used to pass values into the procedure.

Default Mode: If no mode is specified, parameters are considered IN.

Behavior: Values passed to the procedure cannot be modified inside the procedure.

OUT (Output Parameters)

Purpose: Used to return values from the procedure.

Behavior: Parameters can be modified inside the procedure and are returned to the caller. You cannot send an initial value.

INOUT (Input/Output Parameters)

Purpose: Used to pass values into the procedure and modify them to return updated values.

Behavior: Combines the behavior of IN and OUT. You must send an initial value regardless of the output.

Grammar Syntax

[ argname ] [ argmode ] argtype

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE SP_PARAMS(
IN PARAM1 INTEGER,
OUT PARAM2 INTEGER,
INOUT PARAM3 INTEGER)
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

Output Code:

Snowconvert Output

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM1 INTEGER, PARAM2 INTEGER, PARAM3 INTEGER)
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
$$
    BEGIN
        NULL;
        RETURN OBJECT_CONSTRUCT('PARAM2', :PARAM2, 'PARAM3', :PARAM3);
    END;
$$;

Unsupported Scenarios

Input Code:

Redshift

IN -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE SP_PARAMS(
OUT PARAM2 REFCURSOR
)
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

Output Code:

Snowconvert Output

OUT -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM2 REFCURSOR !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!)
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
$$
    BEGIN
        NULL;
        RETURN OBJECT_CONSTRUCT('PARAM2', :PARAM2);
    END;
$$;

Known Issues

There are no known issues.

Related EWIs

1. SCC-EWI-0028 : Type not supported by Snowflake.