MSCEWI1082

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 .

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;
$$;

Recommendations

No additional user actions are required.

PreviousMSCEWI1081 NextMSCEWI1083

Last updated 1 year ago

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;

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; $$;