Comment on page
MSCEWI1079
Parameter names were removed from call
Low
This EWI is used when a parameter's name is removed. This is because the named parameters, while they are accepted, they are not functional in Snowflake meaning they will behave just like positional parameters.
While normally this may not cause issues, it does mean that optional parameters may have to be added to the call and the order of the parameters needs to be checked.
-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE proc_optional_parameters (param1 INTEGER, param2 INTEGER := 8, param3 INTEGER)
AS
BEGIN
INSERT INTO procedure_call_test_table VALUES (param1);
INSERT INTO procedure_call_test_table VALUES (param2);
INSERT INTO procedure_call_test_table VALUES (param3);
END;
CREATE OR REPLACE PROCEDURE calling_procedure
AS
BEGIN
-- positional convention
proc_optional_parameters(1, 2, 3);
-- named convention, names can be safely removed
proc_optional_parameters(param1 => 4, param2 => 5, param3 => 6);
-- named convention, second gets ommited, needs manual intervention
proc_optional_parameters(param1 => 7, param3 => 9);
-- named convention, different order
proc_optional_parameters(param3 => 12, param1 => 10, param2 => 11);
END;
CALL calling_procedure();
-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE PUBLIC.proc_optional_parameters (param1 INTEGER, param2 INTEGER, param3 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
INSERT INTO PUBLIC.procedure_call_test_table VALUES (:param1);
INSERT INTO PUBLIC.procedure_call_test_table VALUES (:param2);
INSERT INTO PUBLIC.procedure_call_test_table VALUES (:param3);
END;
$$;
CREATE OR REPLACE PROCEDURE PUBLIC.calling_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
-- positional convention
CALL proc_optional_parameters(1, 2, 3);
-- named convention, names can be safely removed
/*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
CALL proc_optional_parameters( 4, 5, 6);
-- named convention, second gets ommited, needs manual intervention
/*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
CALL proc_optional_parameters( 7, 9);
-- named convention, second gets ommited, needs manual intervention
/*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
CALL proc_optional_parameters( 12, 10, 11);
END;
$$;
CALL PUBLIC.calling_procedure();
- Make sure to fill in any optional formal parameters and verify all the actual parameters are in the same order as their formal counterparts.