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 );
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
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 FLOAT)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
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":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
LET mytestvar NUMBER(38, 18);
!!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR demo.proc_with_single_output_parameters. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
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 FLOAT,
-- INOUT
PARAM2 FLOAT)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
param1 := param2;
param2 := 32;
RETURN null;
END;
$$;
CREATE OR REPLACE PROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
LET var1 NUMBER(38, 18);
LET var2 NUMBER(38, 18);
var2 := 34;
!!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR demo.proc_with_multiple_output_parameters. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
CALL demo.proc_with_multiple_output_parameters(:var1, :var2);
INSERT INTO demo.TABLE20
VALUES (:var1, :var2);
END;
$$;
Customer data type OUT parameters
when the output parameter is a customer type, the process is similar to a regular data type.
IN -> Teradata_04.sql
CREATE OR REPLACE PROCEDURE GetEmployeeInfo (
OUT EmpInfo EmployeeType
)
BEGIN
SET EmpInfo.EmployeeID = 1001;
SET EmpInfo.FirstName = 'John';
END;
OUT -> Teradata_04.sql
CREATE OR REPLACE PROCEDURE GetEmployeeInfo (
-- OUT
EMPINFO EmployeeType
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
EmpInfo.EmployeeID := 1001;
EmpInfo.FirstName := 'John';
RETURN EmpInfo;
END;
$$;
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.
