CALL¶
Calls a stored procedure.
- See also:
Syntax¶
CALL <procedure_name> ( [ <arg> , ... ] )
[ INTO :<snowflake_scripting_variable> ]
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);
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 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;
$$
;
For more extensive examples of creating and calling stored procedures, see Working with Stored Procedures.