CALL¶

Syntax¶

CALL <procedure_name> ( [ [ <arg_name> => ] <arg> , ... ] )
  [ INTO :<snowflake_scripting_variable> ]

Required parameters¶

procedure_name ( [ [ arg_name => ] arg , ... ] )

Specifies the identifier (procedure_name) for the procedure to call and any input arguments.

You can either specify the input arguments by name (arg_name => arg) or by position (arg).

Note the following:

• You must either specify all arguments by name or by position. You cannot specify some of the arguments by name and other arguments by position.

  When specifying an argument by name, you cannot use double quotes around the argument name.

• If two functions or two procedures have the same name but different argument types, you can use the argument names to specify which function or procedure to execute, if the argument names are different. Refer to Overloading procedures and functions.

Optional parameters¶

INTO :snowflake_scripting_variable

Sets the specified Snowflake Scripting variable to the return value of the stored procedure.

Usage notes¶

• Procedure names are not necessarily unique within the schema; stored procedures are identified and resolved by their arguments types as well as their names (i.e. stored procedures can be overloaded).

• Outside of a Snowflake Scripting block, the value returned by the stored procedure cannot be used, because the call cannot be part of an expression.

  In a Snowflake Scripting block, you can specify INTO :snowflake_scripting_variable to capture the return value from the stored procedure in a Snowflake Scripting variable.

• Stored procedures are not atomic; if one statement in a stored procedure fails, the other statements in the stored procedure are not necessarily rolled back. For information about stored procedures and transactions, see Transaction management.

• You can also create and call an anonymous procedure using CALL (with anonymous procedure).

Examples¶

The following example calls a stored procedure named sv_proc1 and passes in a string literal and number as input arguments. The example specifies the arguments by position:

CALL sv_proc1('Manitoba', 127.4);

You can also specify the arguments by their names:

CALL sv_proc1(province => 'Manitoba', amount => 127.4);

The following example demonstrates how to set and pass a session variable as an input argument to a stored procedure:

SET Variable1 = 49;
CALL sv_proc2($Variable1);

The following is an example of a Snowflake Scripting block that captures the return value of a stored procedure in a Snowflake Scripting variable.

DECLARE
  ret1 NUMBER;
BEGIN
  CALL sv_proc1('Manitoba', 127.4) into :ret1;
  RETURN ret1;
END;

Note: If you are using SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):

EXECUTE IMMEDIATE $$
DECLARE
  ret1 NUMBER;
BEGIN
  CALL sv_proc1('Manitoba', 127.4) into :ret1;
  RETURN ret1;
END;
$$
;

For more extensive examples of creating and calling stored procedures, see Working with stored procedures.