Stored Procedures and UDTFs: Argument Names Respected in Calls¶

Calls to stored procedures and user-defined table functions (UDTFs), where the call includes named arguments, Snowflake will give precedence to argument names over argument position as follows.

Previously:

When calling procedures or user-defined table functions (UDTFs) that have named arguments, Snowflake ignores argument names and uses argument position to determine which value is passed to the procedure or function.

Currently:

Argument names will take precedence over argument position in calls to procedures and UDTFs. This may result in the following behavior that differs from before the behavior change:

• A previously-working call to a stored procedure or user-defined table function could result in an error.

• Argument names could cause arguments to be passed in a different order than previously. This could result in an error, wrong results, or wrong data insertion.

• Argument names could cause a different stored procedure with the same name to be called. This could result in an error, wrong results, or wrong data insertion.

• The following example illustrates how calls to two stored procedures might differ before and after the change.

The following example illustrates how calls to two stored procedures might differ before and after the change.

-- One stored procedure
CREATE OR REPLACE PROCEDURE proc(STRARG1 varchar, STRARG2 varchar)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  return 'hello';
END
$$;
-- Another procedure
CREATE OR REPLACE PROCEDURE proc(ARG1 number, ARG2 number)
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
BEGIN
  return ARG1 < ARG2;
END
$$;
-- Example A
-- Before: returns 'hello'
-- After: returns TRUE
CALL PROC(ARG1 => '5', ARG2 => '100');
-- Example B
-- Before: returns TRUE
-- After: returns 'hello'
CALL PROC(STRARG1 => 5, STRARG2 => 100); -- 'hello'.

Copy

Ref: 1017