MSCEWI1079

Parameter names were removed from call

This is a deprecated version of the SnowConvert documentation, please visit the official site .

Severity

Low

Status: Deprecated

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.

PreviousMSCEWI1078 NextMSCEWI1080

Last updated 1 year ago

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