Writing stored procedures in Snowflake Scripting¶

Simple example of using arguments passed to a stored procedure¶

The following stored procedure uses the values of the arguments in IF and RETURN statements.

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;

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

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;
$$
;

The following is an example of calling the stored procedure:

CALL return_greater(2, 3);

Using an argument in a SQL statement (binding)¶

As is the case with Snowflake Scripting variables, if you need to use an argument in a SQL statement, put a colon (:) in front of the argument name. For more information, see Using a variable in a SQL statement (binding).

The following sections contain examples that use bind variables in stored procedures:

• Example that uses a bind variable in a WHERE clause

• Example of using a bind variable to set the value of a property

• Example that uses bind variables to set parameters in a command

• Examples that use a bind variable for an array

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;
$$
;

CALL find_invoice_by_id('2');

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;

CALL test_bind_comment('My Test Table');

SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';

+---------------+
| COMMENT       |
|---------------|
| My Test Table |
+---------------+

CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;

CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;
$$
;

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');

Using an argument as an object identifier¶

If you need to use an argument to refer to an object (for example, a table name in the FROM clause of a SELECT statement), use the IDENTIFIER keyword to indicate that the argument represents an object identifier. For example:

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;

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

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;
$$
;

The following is an example of calling the stored procedure:

CALL get_row_count('invoices');

The following example executes a CREATE TABLE … AS SELECT (CTAS) statement in a stored procedure based on the table names provided in arguments.

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;

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

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;
$$
;

Before calling the procedure, create a simple table and insert data:

CREATE OR REPLACE TABLE test_table_for_ctas_sp (
  id NUMBER(2),
  v  VARCHAR(2))
AS SELECT
  column1,
  column2,
FROM
  VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c');

Call the stored procedure to create a new table that is based on this table:

CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');

Using an argument when building a string for a SQL statement¶

Note that if you are building a SQL statement as a string to be passed to EXECUTE IMMEDIATE (see Assigning a query to a declared RESULTSET), do not prefix the argument with a colon. For example:

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Calling a stored procedure without using the returned value¶

Use a CALL statement to call the stored procedure (as you normally would).

If you need to pass in any variables or arguments as input arguments in the CALL statement, remember to use a colon (:) in front of the variable name. (See Using a variable in a SQL statement (binding).)

The following is an example of a stored procedure that calls another stored procedure but does not depend on the return value.

First, create a table for use in the example:

-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);

Then, create the stored procedure that you will call from another stored procedure:

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;

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

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;

Next, create a second stored procedure that calls the first stored procedure:

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;

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

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;
$$
;

Finally, call the second stored procedure:

CALL insert_two_values(4, 5);

Using the value returned from a stored procedure call¶

If are calling a stored procedure that returns a scalar value and you need to access that value, use the INTO :snowflake_scripting_variable clause in the CALL statement to capture the value in a Snowflake Scripting variable.

The following example calls the get_row_count stored procedure that was defined in Using an argument as an object identifier.

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;

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

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  $$
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;
  $$
  ;

The following is an example of calling the stored procedure:

CALL count_greater_than('invoices', 3);

If the stored procedure returns a table, you can capture the return value by setting a RESULTSET to a string containing the CALL statement. (See Assigning a query to a declared RESULTSET.)

To retrieve the return value from the call, you can use a CURSOR for the RESULTSET. For example:

DECLARE
  res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
  IF (row_variable.col1 > 0) THEN
    ...;
  ELSE
    ...;
  END IF;
END FOR;
...

Benefits of nested stored procedures¶

Nested stored procedures provide the following benefits:

• They can enhance and simplify security by encapsulating logic inside an anonymous block or parent stored procedure, which prevents access to it from outside the block or parent.

• They keep code modular by splitting it logically into smaller chunks, which can make it easier to maintain and debug.

• They improve maintainability by reducing the need for global variables or additional arguments, because a nested stored procedure can directly access the local variables of its block.

Usage notes for calling nested stored procedures¶

The following usage notes apply to calling a nested stored procedure:

• To pass arguments to a nested stored procedure, a block can use constant values, Snowflake Scripting variables, bind variables, SQL (session) variables, and calls to user-defined functions.

• When there is a mismatch between the data type of the value being passed in and the data type of an argument, Snowflake performs supported coercions automatically. For information about which coercions Snowflake can perform automatically, see Data type conversion.

Usage notes for variables in a nested stored procedure¶

The following usage notes apply to variables in a nested stored procedure:

• A nested stored procedure can reference variables from its block that were declared before the nested stored procedure declaration in the DECLARE section of its block. It can’t reference variables declared after it in the DECLARE section.

• A nested stored procedure can’t access variables declared in a LET statement in the BEGIN … END section of a block.

• The value of a referenced variable reflects its value at the time when the nested stored procedure is called.

• A nested stored procedure can modify a referenced variable value, and the modified value persists in the block and across multiple invocations of the same nested procedure in a single execution of its anonymous block or in a single call to its parent stored procedure.

• The value of a variable that was declared before a nested stored procedure call can be passed as an argument to the nested stored procedure. The variable value can be passed as an argument in a call even if the variable was declared after the nested stored procedure declaration or in a LET statement.

For example, the following stored procedure declares several variables:

CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  var_before_nested_proc NUMBER DEFAULT 1;
  test_nested_variables PROCEDURE(arg1 NUMBER)
    -- <nested_sp_logic>
  var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
  LET var_let_before_call NUMBER DEFAULT 3;
  LET result := CALL nested_proc(:<var_name>);
  LET var_let_after_call NUMBER DEFAULT 3;
  RETURN result;
END;
$$;

In this example, only var_before_nested_proc can be referenced in nested_sp_logic.

In the nested stored procedure call, the value of any of the following variables can be passed to the nested stored procedure as an argument in var_name:

• var_before_nested_proc

• var_after_nested_proc

• var_let_before_call

The value of var_let_after_call can’t be passed to the nested stored procedure as an argument.

Limitations for nested stored procedures¶

The following limitations apply to defining nested stored procedures:

• They can’t be defined inside other nested stored procedures or inside control structures, such as FOR or WHILE loops.

• Each nested stored procedure must have a unique name in its block. That is, nested stored procedures can’t be overloaded.

• They don’t support output (OUT) arguments.

• They don’t support optional arguments with default values.

The following limitations apply to calling nested stored procedures:

• They can’t be called in an EXECUTE IMMEDIATE statement.

• They can’t be called in asynchronous child jobs.

• They don’t support named input arguments (arg_name => arg). Arguments must be specified by position. For more information, see CALL.

Examples of nested stored procedures¶

The following examples use nested stored procedures:

• Define a nested stored procedure that returns tabular data

• Define a nested stored procedure that returns a scalar value

• Define a nested stored procedure in an anonymous block

• Define a nested stored procedure that is passed arguments

• Define a nested stored procedure that calls another nested stored procedure

CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
  nested_return_table PROCEDURE()
    RETURNS TABLE()
    AS
    DECLARE
      res2 RESULTSET;
    BEGIN
      INSERT INTO nested_table VALUES(1);
      INSERT INTO nested_table VALUES(2);
      res2 := (SELECT * FROM nested_table);
      RETURN TABLE(res2);
    END;
BEGIN
  CREATE OR REPLACE TABLE nested_table(col1 INT);
  res := (CALL nested_return_table());
  RETURN TABLE(res);
END;
$$;

CALL nested_procedure_example_table();

+------+
| COL1 |
|------|
|    1 |
|    2 |
+------+

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

CALL nested_procedure_example_scalar();

+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3                               |
+---------------------------------+

EXECUTE IMMEDIATE $$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  a NUMBER;
  b NUMBER;
  x NUMBER;
  log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
    RETURNS NUMBER
    AS
    BEGIN
      INSERT INTO log_nested_values VALUES(:num1, :num2);
      x := :num1 * :num2;
      RETURN x;
    END;
BEGIN
  a := 5;
  b := 10;
  CALL log_and_multiply_numbers(:a, :b);
  RETURN x;
END;
$$;

CALL nested_procedure_example_arguments();

+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
|                                 50 |
+------------------------------------+

SELECT * FROM log_nested_values;

+------+------+
| COL1 | COL2 |
|------+------|
|    5 |   10 |
+------+------+

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var1 NUMBER := 10;
    BEGIN
      counter := counter + var1;
    END;
  call_counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var2 NUMBER := 15;
    BEGIN
      counter := counter + var2;
      CALL counter_nested_proc();
    END;
BEGIN
  counter := 0;
  CALL counter_nested_proc();
  CALL call_counter_nested_proc();
  RETURN counter;
END;
$$;

CALL nested_procedure_example_call_from_nested();

+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
|                                        35 |
+-------------------------------------------+

Using a SQL variable in a stored procedure¶

The following example uses a SQL variable in a stored procedure.

First, set a SQL variable in a session:

SET example_use_variable = 2;

Create a simple stored procedure that runs with caller’s rights and uses this SQL variable:

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;

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

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;
$$
;

Call the stored procedure:

CALL use_sql_variable_proc();

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                     4 |
+-----------------------+

Set the SQL variable to a different value:

SET example_use_variable = 9;

Call the procedure again to see that the returned value has changed:

CALL use_sql_variable_proc();

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                    18 |
+-----------------------+

Setting a SQL variable in a stored procedure¶

You can set a SQL variable in a stored procedure that’s running with caller’s rights. For more information, including guidelines for using SQL variables in stored procedures, see Caller’s rights stored procedures.

The following example sets a SQL variable in a stored procedure.

First, set a SQL variable in a session:

SET example_set_variable = 55;

Confirm the value of the SQL variable:

SHOW VARIABLES LIKE 'example_set_variable';

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+

For example, the following stored procedure sets the SQL variable example_set_variable to a new value and returns the new value:

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;

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

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;
$$
;

Call the stored procedure:

CALL set_sql_variable_proc();

+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
|                    52 |
+-----------------------+

Confirm the new value of the SQL variable:

SHOW VARIABLES LIKE 'example_set_variable';

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+