Auto-Generated code in order to support output parameters in Snowflake Scripting
This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Low
Description
This warning is used to inform that some statements are being added to the procedures with output parameters in order to emulate their functionality. The auto-generated code is being added in the procedure with the output parameters and also where this procedure is being called.
Example Code
Input
CREATE OR REPLACE PROCEDURE proc_with_output_parameters(param1 OUT NUMBER, param2 IN OUT NUMBER)
IS
BEGIN
param1 := 123;
param2 := 456;
END;
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_output_parameters
IS
var1 NUMBER;
var2 NUMBER;
BEGIN
proc_with_output_parameters(var1, var2);
END;
Output
CREATE OR REPLACE PROCEDURE PUBLIC.proc_with_output_parameters (param1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/, param2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/)
RETURNS TABLE(
param1 NUMBER (38,18),
param2 NUMBER (38,18)
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
param1 := 123;
param2 := 456;
/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
LET outParametersResultSet RESULTSET :=
(
SELECT
:param1,
:param2
);
RETURN TABLE(outParametersResultSet);
END;
$$;
CREATE OR REPLACE PROCEDURE PUBLIC.proc_calling_proc_with_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
var1 NUMBER (38,18);
var2 NUMBER (38,18);
BEGIN
CALL PUBLIC.proc_with_output_parameters(:var1, :var2);
/*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
LET call_results VARIANT;
SELECT
OBJECT_CONSTRUCT(*)
INTO
call_results
FROM
TABLE(RESULT_SCAN(LAST_QUERY_ID()));
var1 := GET(:call_results, ':PARAM1::NUMBER(38,18)');
var2 := GET(:call_results, ':PARAM2::NUMBER(38,18)');
END;
$$;
