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;

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

Using an output argument to return a single value¶

The following example creates the stored procedure simple_out_sp_demo with the output argument xout in its definition. The stored procedure sets the value of xout to 2.

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  xout := 2;
  RETURN 'Done';
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 simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  xout := 2;
  RETURN 'Done';
END;
$$
;

The following anonymous block sets the value of the x variable to 1. Then, it calls the simple_out_sp_demo stored procedure and specifies the variable as the argument.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
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):

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
$$
;

The output shows that the simple_out_sp_demo stored procedure performed an operation to set the value of the output argument to 2 and then returned this value to the anonymous block.

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

The following anonymous block calls simple_out_sp_demo stored procedure and returns an error, because it tries to assign a value to the output argument using an expression instead of a variable.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
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):

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
$$
;

Using output arguments to return several values for multiple calls to a stored procedure¶

The following example demonstrates the following behavior related to stored procedures and input and output arguments:

• A stored procedure can have several input and output arguments in its definition.

• A program can call a stored procedure with output arguments multiple times, and the values of the output arguments are preserved after each call.

• Input arguments don’t return values to the calling program.

Create the stored procedure multiple_out_sp_demo with multiple input and output arguments in its definition. The stored procedure performs the same operations on the equivalent input and output arguments. For example, the stored procedure adds 1 to the p1_in input argument and to the p1_out output argument.

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
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 multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
$$
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
$$
;

The following anonymous block assigns values to the variables that correspond to the arguments of the multiple_out_sp_demo stored procedure and then calls the stored procedure multiple times. The first call uses the variable values specified in the anonymous block, but each subsequent call uses the values returned by the output arguments in the multiple_out_sp_demo stored procedure.

BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
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):

EXECUTE IMMEDIATE
$$
BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;

+------------------------+
| anonymous block        |
|------------------------|
| [                      |
|   1,                   |
|   4,                   |
|   "hello",             |
|   "hello hi  hi  hi ", |
|   true,                |
|   false                |
| ]                      |
+------------------------+

Using an output argument with a different data type than the input value from a calling procedure¶

For some use cases, there might be a mismatch between the data type of the value being passed in to a stored procedure and the data type of the procedure’s output argument. In these cases, supported coercions are performed automatically.

This example demonstrates automatic conversion of a FLOAT value that is passed to an output argument with a NUMBER data type. The FLOAT value is automatically converted to a NUMBER value and then passed back to the calling anonymous block.

Create the sp_out_coercion stored procedure, which takes an output argument of type NUMBER:

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  x := x * 2;
  RETURN 'Done';
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 sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
$$
;

Execute an anonymous block that passes a FLOAT value to the sp_out_coercion stored procedure:

BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
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):

EXECUTE IMMEDIATE
$$
BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;

The output shows both the returned value and the data type of the returned value, by calling the SYSTEM$TYPEOF function. Note that the value is coerced from a NUMBER value back to a FLOAT value after it is returned from the stored procedure:

+---------------------------+
| anonymous block           |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+

Using an output argument to return the total sales for an employee in a quarter¶

This example uses the following quarterly_sales table:

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Create the stored procedure sales_total_out_sp_demo that takes two input arguments for the employee identifier and quarter, and one output argument to calculate the sales total for the given employee and quarter.

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
    id INT,
    quarter VARCHAR(20),
    total_sales OUT NUMBER(38,0))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  SELECT SUM(amount) INTO total_sales FROM quarterly_sales
    WHERE empid = :id AND
          quarter = :quarter;
  RETURN 'Done';
END;
$$
;

Create the stored procedure emp_quarter_calling_sp_demo that calls the sales_total_out_sp_demo stored procedure. This stored procedure also takes two input arguments for the employee identifier and quarter.

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
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 emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;

Call the emp_quarter_calling_sp_demo with the arguments 2 (for the employee identifier) and '2023_Q4' (for the quarter).

CALL emp_quarter_calling_sp_demo(2, '2023_Q4');

+-----------------------------------------------------+
| emp_quarter_calling_sp_demo                         |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+

Define a nested stored procedure that returns tabular data¶

The following example defines a nested stored procedure that returns a tabular data. The example creates a parent stored procedure called nested_procedure_example_table with a nested stored procedure called nested_return_table. The code includes the following logic:

• Declares a variable called res of type RESULTSET.

• Includes the following logic in the nested stored procedure:

  • Declares a variable called res2.

  • Inserts values into a table called nested_table.

  • Sets the res2 variable to the results of a SELECT on the table.

  • Returns the tabular data in the result set.

• Creates the table nested_table in the parent stored procedure.

• Calls the nested stored procedure nested_return_table and sets the res variable to the results of the call to the nested stored procedure.

• Returns the tabular results in the res variable.

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 the stored procedure:

CALL nested_procedure_example_table();

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

Define a nested stored procedure that returns a scalar value¶

The following example defines a nested stored procedure that returns a scalar value. The example creates a parent stored procedure called nested_procedure_example_scalar with a nested stored procedure called simple_counter. The code includes the following logic:

• Declares a variable called counter of type NUMBER, and sets the value of this variable to 0.

• Specifies that the nested stored procedure adds 1 to the current value of the counter variable.

• Calls the nested stored procedure three times in the parent stored procedure. The value of the counter variable is carried over between invocations of the nested stored procedure.

• Returns the value of the counter variable, which is 3.

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 the stored procedure:

CALL nested_procedure_example_scalar();

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

Define a nested stored procedure in an anonymous block¶

The following example is the same as the example in Define a nested stored procedure that returns a scalar value, except that it defines a nested stored procedure in an anonymous block instead of a stored procedure:

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 |
+-----------------+

Define a nested stored procedure that is passed arguments¶

The following example defines a nested stored procedure that is passed arguments. In the example, the nested stored procedure inserts values into the following table:

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

The example creates a parent stored procedure called nested_procedure_example_arguments with a nested stored procedure called log_and_multiply_numbers. The nested stored procedure takes two arguments of type NUMBER. The code includes the following logic:

• Declares variables a, b, and x of type NUMBER.

• Includes a nested stored procedure that performs the following actions:

  • Inserts the two number values passed to it by the parent stored procedure into the log_nested_values table using bind variables.

  • Sets the value of variable x to the result of multiplying the two argument values.

  • Returns the value of x to the parent stored procedure.

• Sets the value of variable a to 5 and variable b to 10.

• Calls the nested stored procedure.

• Returns the value of the x variable, which was set in the nested stored procedure.

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 the stored procedure:

CALL nested_procedure_example_arguments();

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

Query the log_nested_values table to confirm that the nested stored procedure inserted the values passed to it:

SELECT * FROM log_nested_values;

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

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

The following example defines a nested stored procedure that calls another nested stored procedure. The example creates a parent stored procedure called nested_procedure_example_call_from_nested with two nested stored procedures called counter_nested_proc and call_counter_nested_proc. The code includes the following logic:

• Declares a variable called counter of type NUMBER, and sets the value of this variable to 0.

• Includes the nested stored procedure counter_nested_proc that adds 10 to the value of counter.

• Includes the nested stored procedure call_counter_nested_proc that adds 15 to the value of counter and also calls counter_nested_proc (which adds another 10 to the value of counter).

• Calls both nested stored procedures in the parent stored procedure.

• Returns the value of the counter variable, which is 35.

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 the stored procedure:

CALL nested_procedure_example_call_from_nested();

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