CALL

Calls a stored procedure.

See also:

CREATE PROCEDURE , SHOW PROCEDURES

Syntax

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

Required Parameters

procedure_name ( [ arg , ... ] )

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

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:

CALL sv_proc1('Manitoba', 127.4);
Copy

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

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;
Copy

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

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

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

Language: English