OUTPUT PARAMETERS

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

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. Since the output parameters are not supported by Snowflake Scripting, a solution has been implemented in order to emulate their functionality.

Sample Source Patterns

Sample auxiliary table

IN -> Teradata_01.sql

CREATE TABLE table20 ( col1 NUMBER, col2 NUMBER );

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE table20 (
col1 NUMBER(38, 18),
col2 NUMBER(38, 18)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

In the declaration, the OUT or IN OUT keywords are removed. The assignment is being emitted the same as the input, but to emulate the functionality of the output parameter, some statements are being added.

When a procedure with output parameters is called into another one, some statements are added to get and assign the value(s) to the respective argument(s).

Single out parameter

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;

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE demo.proc_with_single_output_parameters (
--                                                                     OUT
                                                                         PARAM1 NUMBER(38, 18))
RETURNS VARIANT
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;
  RETURN null;
 END;
$$;

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

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;

OUT -> Teradata_03.sql

CREATE OR REPLACE PROCEDURE demo.proc_with_multiple_output_parameters (
--                                                                       OUT
                                                                           PARAM1 NUMBER(38, 18),
--                                                                                                  INOUT
                                                                                                        PARAM2 NUMBER(38, 18))
RETURNS VARIANT
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 := param2;
    param2 := 32;
    RETURN null;
  END;
$$;