Links

MSCEWI1079

Parameter names were removed from call

Severity

Low

Description

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.

Example Code

Oracle:

-- 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();

Snowflake Scripting:

-- 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();

Recommendations

  • Make sure to fill in any optional formal parameters and verify all the actual parameters are in the same order as their formal counterparts.
  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.