OUTPUT PARAMETERS

This article is about the current transformation of the output parameters and how their functionality is being emulated.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

Description

An output parameter is a parameter whose value is passed out of the stored procedure, back to the calling statement. Snowflake has direct support for output parameters.

Sample Source Patterns

Single out parameter

Teradata

IN -> Teradata_02.sql

CREATE PROCEDURE demo.proc_with_single_output_parameters(OUT param1 NUMBER)
BEGIN
 SET param1 = 100;
END;

REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
BEGIN
  DECLARE mytestvar NUMBER;
  CALL demo.proc_with_single_output_parameters(mytestvar);
  INSERT INTO demo.TABLE20 VALUES(mytestvar,432);
END;

Snowflake

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE demo.proc_with_single_output_parameters (PARAM1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 BEGIN
  param1 := 100;
 END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "demo.TABLE20" **
CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/24/2024" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
  mytestvar NUMBER(38, 18);
 BEGIN
   
  CALL demo.proc_with_single_output_parameters(:mytestvar);
  INSERT INTO demo.TABLE20
  VALUES (:mytestvar,432);
 END;
$$;

Multiple out parameter

Teradata

IN -> Teradata_03.sql

CREATE PROCEDURE demo.proc_with_multiple_output_parameters(OUT param1 NUMBER, INOUT param2 NUMBER)
BEGIN
  SET param1 = param2;
  SET param2 = 32;
END;

CREATE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
BEGIN
    DECLARE var1  NUMBER;
    DECLARE var2  NUMBER;
    SET var2 = 34;
    CALL demo.proc_with_multiple_output_parameters(var1, var2);
    INSERT INTO demo.TABLE20 VALUES(var1,var2);
END;

Snowflake

OUT -> Teradata_03.sql

CREATE OR REPLACE PROCEDURE demo.proc_with_multiple_output_parameters (PARAM1 OUT NUMBER(38, 18), PARAM2 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    param1 := param2;
    param2 := 32;
  END;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "demo.TABLE20" **
CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    var1 NUMBER(38, 18);
    var2 NUMBER(38, 18);
  BEGIN
     
     
    var2 := 34;
    CALL demo.proc_with_multiple_output_parameters(:var1, :var2);
    INSERT INTO demo.TABLE20
    VALUES (:var1, :var2);
  END;
$$;

Related EWIs

No related EWIs.