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.
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).
CREATEORREPLACEPROCEDURE demo.proc_with_single_output_parameters (/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/ PARAM1 FLOAT)RETURNS VARIANTLANGUAGESQLEXECUTEASCALLERAS $$BEGIN param1 :=100;/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/RETURN param1;END; $$;CREATEORREPLACEPROCEDURE demo.proc_calling_proc_with_single_output_parameters ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS $$DECLARE call_results VARIANT;BEGIN LET mytestvar NUMBER(38, 18); call_results := (CALL demo.proc_with_single_output_parameters(:mytestvar) );/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/ mytestvar := :call_results;INSERT INTO demo.TABLE20VALUES (:mytestvar,432);END; $$;
CREATEORREPLACEPROCEDURE demo.proc_with_multiple_output_parameters (/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/ PARAM1 FLOAT,/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/ PARAM2 FLOAT)RETURNS VARIANTLANGUAGESQLEXECUTEASCALLERAS $$BEGIN param1 := param2; param2 :=32;/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);END; $$;CREATEORREPLACEPROCEDURE demo.proc_calling_proc_with_multiple_output_parameters ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS $$DECLARE call_results VARIANT;BEGIN LET var1 NUMBER(38, 18); LET var2 NUMBER(38, 18); var2 :=34; call_results := (CALL demo.proc_with_multiple_output_parameters(:var1, :var2) );/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/ var1 := :call_results:param1; var2 := :call_results:param2;INSERT INTO demo.TABLE20VALUES (: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.
CREATEORREPLACEPROCEDURE GetEmployeeInfo (-- OUT /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/ EMPINFO EmployeeType)RETURNS VARIANTLANGUAGESQLEXECUTEASCALLERAS$$BEGIN EmpInfo.EmployeeID :=1001; EmpInfo.FirstName :='John'; --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
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.
Related EWIs
MSCEWI1087: Arguments do not match the called procedure parameters count, default parameters are not supported yet.