Procedure calls can be migrated to Snowflake as long as there are no optional parameters and their order matches the formal parameters. Please note that Procedure invocations get migrated to a Call statement.
Consider the next table and procedure for the examples below.
CREATETABLEprocedure_call_test_table( col1 INTEGER);-- Simple Called procedureCREATEORREPLACEPROCEDURE called_procedure (param1 INTEGER)ASBEGININSERT INTO procedure_call_test_table VALUES (param1);END;
CREATE OR REPLACETABLEPUBLIC.procedure_call_test_table (col1 INTEGER);-- Simple Called procedureCREATEORREPLACEPROCEDURE PUBLIC.called_procedure (param1 INTEGER)RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$BEGININSERT INTO PUBLIC.procedure_call_test_table VALUES (:param1);END;$$;
This sample contains manual intervention for some functional differences and is used to explain them. For more information on these differences, please check the Known Issues section below.
Oracle
-- Procedure with optional parametersCREATEORREPLACEPROCEDURE proc_optional_parameters (param1 INTEGER, param2 INTEGER :=8, param3 INTEGER)ASBEGININSERT INTO procedure_call_test_table VALUES (param1);INSERT INTO procedure_call_test_table VALUES (param2);INSERT INTO procedure_call_test_table VALUES (param3);END;CREATEORREPLACEPROCEDURE calling_procedureASBEGIN-- positional convention proc_optional_parameters(1, 2, 3);-- named convention proc_optional_parameters(param1 =>4, param2 =>5, param3 =>6);-- named convention, second gets ommited proc_optional_parameters(param1 =>7, param3 =>9);-- named convention, different order proc_optional_parameters(param3 =>12, param1 =>10, param2 =>11);END;CALL calling_procedure();SELECT*FROM procedure_call_test_table;
1. Calling Subprograms with default values is not supported
Snowflake does not support setting default values for parameters. So these will need to be filled into every call.
2. Named parameters are accepted, but not functionally equivalent
These parameters will not cause any compilation errors when run in Snowflake; however, calls still place them in a positional manner. For this reason, the order of these parameters needs to be checked. SnowConvert does not support checking nor reordering these parameters.
3. Calling Subprograms with Out Parameters is not supported
Snowflake does not have support for parameter modes, however, a solution is being implemented to emulate their functionality. To get more information about the transformation for output parameters please go to the following article Output Parameters.
Related EWIs
MSCEWI1058: Functionality is not currently supported by Snowflake Scripting
MSCEWI1078: Output parameters are not supported by Snowflake Scripting