OUTPUT PARAMETERS

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

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

Teradata

CREATE TABLE table20 ( col1 NUMBER, col2 NUMBER );

Snowflake

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 being called into another one, some statements are added in order to get and assign the value(s) to the respective argument(s).

Single out parameter

Teradata

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

Multiple out parameter

Teradata

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

Customer data type OUT parameters

when the output parameter is a customer type, the process is similar to a regular data type.

Teradata

CREATE OR REPLACE PROCEDURE GetEmployeeInfo (
    OUT EmpInfo EmployeeType
)
BEGIN
    SET EmpInfo.EmployeeID = 1001;
    SET EmpInfo.FirstName = 'John';
END;

Snowflake

Known Issues

1. Some data types may not work properly

As seen in the transformation, when retrieving the value from the called procedures, an implicit cast is performed from VARIANT to the type specified by the variable. Since there are a lot of possible data types, some casts may fail or contain different data.

Related EWIs

1. MSCEWI1082: Auto-Generated code in order to emulate the output parameters functionality.

2. MSCEWI1083: Output parameters are not supported by Snowflake Scripting but their functionality is being emulated.

3. MSCEWI1087: Arguments do not match the called procedure parameters count, default parameters are not supported yet.