Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/developer-guide/python-connector/../../sql-reference/sql/call below:

Website Navigation

• Reference
• SQL command reference
• Functions, procedures, & scripting

CALL | Snowflake Documentation

Calls a stored procedure.

See also:

CREATE PROCEDURE , SHOW PROCEDURES

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

Copy

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.

INTO :snowflake_scripting_variable

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

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

CALL stproc1(5.14::FLOAT);

Copy

Each argument to a stored procedure can be a general expression:

CALL stproc1(2 * 5.14::FLOAT);

Copy

An argument can be a subquery:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

Copy

You can call only one stored procedure per CALL statement. For example, the following statement fails:

CALL proc1(1), proc2(2);                          -- Not allowed

Copy

Also, you cannot use a stored procedure CALL as part of an expression. For example, all the following statements fail:

CALL proc1(1) + proc1(2);                         -- Not allowed
CALL proc1(1) + 1;                                -- Not allowed
CALL proc1(proc2(x));                             -- Not allowed
SELECT * FROM (call proc1(1));                    -- Not allowed

Copy

However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively.

Caution

Nested calls can exceed the maximum allowed stack depth, so be careful when nesting calls, especially when using recursion.

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

Copy

You can also specify the arguments by their names:

CALL sv_proc1(province => 'Manitoba', amount => 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 use Snowflake CLI, 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 Snowflake CLI, SnowSQL, the Classic Console, and Python Connector):

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

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4