CALL

Description

Runs a stored procedure. The CALL command must include the procedure name and the input argument values. You must call a stored procedure by using the CALL statement. (Redshift SQL Language Reference CALL).

This syntax is partially supported in Snowflake. Procedure calls using output parameters will be tagged with the SSC-EWI-0073. This transformation will be delivered in the future.

Grammar Syntax

CALL sp_name ( [ argument ] [, ...] )

Sample Source Patterns

Base scenario

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE PROCEDURE sp_insert_values(IN arg1 INT, IN arg2 DATE)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO event VALUES (arg1, arg2);
END;
$$;

CALL sp_insert_values(1, CURRENT_DATE);

Output Code:

Redshift

OUT -> Redshift_01.sql

CREATE PROCEDURE sp_insert_values (arg1 INT, arg2 DATE)
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
$$
BEGIN
    INSERT INTO event
    VALUES (:arg1, : arg2);
END;
$$;

CALL sp_insert_values(1, CURRENT_DATE());

Call using Output Parameters Mode (INOUT, OUT)

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE sp_calculate_sum_product(IN a NUMERIC, IN b NUMERIC, INOUT sum_result NUMERIC, INOUT product_result NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    sum_result := a + b;
    product_result := a * b;
END;
$$;

CREATE OR REPLACE PROCEDURE call_sp_calculate_sum_product()
LANGUAGE plpgsql
AS $$
DECLARE
    sum_value NUMERIC DEFAULT null;
    product_value NUMERIC DEFAULT null;
BEGIN
    CALL sp_calculate_sum_product(FLOOR(20.5)::NUMERIC, CEIL(20.7)::NUMERIC, sum_value, product_value);
    INSERT INTO test VALUES (sum_value, product_value);
END;
$$;

CALL call_sp_calculate_sum_product();

Output Code:

Redshift

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE sp_calculate_sum_product (a NUMERIC, b NUMERIC, sum_result NUMERIC, product_result NUMERIC)
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 $$
BEGIN
    sum_result := a + b;
    product_result := a * b;
    RETURN OBJECT_CONSTRUCT('sum_result', :sum_result, 'product_result', :product_result);
END;
$$;

CREATE OR REPLACE PROCEDURE call_sp_calculate_sum_product ()
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
    sum_value NUMERIC DEFAULT NULL;
    product_value NUMERIC DEFAULT NULL;
BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CALL' NODE ***/!!!
    CALL sp_calculate_sum_product(FLOOR(20.5), CEIL(20.7), sum_value, product_value);
    INSERT INTO test
    VALUES (:sum_value, : product_value);
END;
$$;

CALL call_sp_calculate_sum_product();

Known Issues

• Output parameters are not currently supported in procedure calls.

Related EWIs.

1. SSC-EWI-0073: Pending Functional Equivalence Review