Working with RESULTSETs¶

This topic explains how to use a RESULTSET in Snowflake Scripting.

Introduction¶

In Snowflake Scripting, a RESULTSET is a SQL data type that points to the result set of a query.

Because a RESULTSET is just a pointer to the results, you must do one of the following to access the results through the RESULTSET:

Use the TABLE(...) syntax to retrieve the results as a table.
Iterate over the RESULTSET with a cursor.

Examples of both of these are included below.

Understanding the differences between a cursor and a RESULTSET¶

A RESULTSET and a cursor both provide access to the result set of a query. However, these objects differ in the following ways:

The point in time when the query is executed.
- For a cursor, the query is executed when you execute the OPEN command on the cursor.
- For a RESULTSET, the query is executed when you assign the query to the RESULTSET (either in the DECLARE section or in the BEGIN … END block).
Support for binding in the OPEN command.
- When you declare a cursor, you can specify bind parameters (? characters). Later, when you execute the OPEN command, you can bind variables to those parameters in the USING clause.
- RESULTSET does not support the OPEN command. However, you can bind variables in SQL commands before returning the result set.

In general, it is simpler to use a RESULTSET when you want to return a table that contains the result set of a query. However, you can also return a table from a Snowflake Scripting block with a cursor. To do so, you can pass the cursor to RESULTSET_FROM_CURSOR(cursor) to return a RESULTSET and pass that RESULTSET to TABLE(...). See Returning a table for a cursor.

Declaring a RESULTSET¶

You can declare a RESULTSET in the DECLARE section of a block or in the BEGIN … END section of the block.

Within the DECLARE section, use the syntax described in RESULTSET declaration syntax. For example:
```
DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
```
Copy
Within the BEGIN … END block, use the syntax described in RESULTSET assignment syntax. For example:
```
BEGIN
  ...
  LET res RESULTSET := (SELECT col1 FROM mytable ORDER BY col1);
```
Copy

Assigning a query to a declared RESULTSET¶

To assign the result of a query to a RESULTSET that has already been declared, use the following syntax:

<resultset_name> := [ ASYNC ] ( <query> ) ;

Copy

Where:

resultset_name
The name of the RESULTSET.

The name must be unique within the current scope.

The name must follow the naming rules for Object identifiers.

ASYNC

Preview Feature — Open

Available to all accounts.

Runs the query as an asynchronous child job.

The query can be any valid SQL statement, including SELECT statements and DML statements, such as INSERT or UPDATE.

When this keyword is omitted, the stored procedure runs child jobs sequentially, and each child job waits for the running child job to finish before it starts.

You can use this keyword to run multiple child jobs concurrently, which can improve efficiency and reduce overall run time.

You can use AWAIT and CANCEL statements to manage asynchronous child jobs for a RESULTSET.

query
The query to assign to the RESULTSET.

To assign a query to a RESULTSET:

DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

To assign a query to a RESULTSET and run the query as an asynchronous child job:

DECLARE
  res RESULTSET;
BEGIN
  res := ASYNC (SELECT col1 FROM mytable ORDER BY col1);
  ...

Copy

To build a SQL string dynamically for the query, set query to (EXECUTE IMMEDIATE string_of_sql). For example:

DECLARE
  res RESULTSET;
  col_name VARCHAR;
  select_statement VARCHAR;
BEGIN
  col_name := 'col1';
  select_statement := 'SELECT ' || col_name || ' FROM mytable';
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

Although you can set query to an EXECUTE IMMEDIATE statement for a RESULTSET, you can’t do this for a cursor.

Using a RESULTSET¶

The query for a RESULTSET is executed when the object is associated with that query. For example:

When you declare a RESULTSET and set the DEFAULT clause to a query, the query is executed at that point in time.
When you use the := operator to assign a query to a RESULTSET, the query is executed at that point in time.

Note

Because a RESULTSET points to the result set of a query (and does not contain the result set of a query), a RESULTSET is valid only as long as the query results are cached (typically 24 hours). For details about query result caching, see Using Persisted Query Results.

Once the query is executed, you can access the results by using a cursor. You can also return the results as a table from a stored procedure.

Using a cursor to access data from a RESULTSET
Returning a RESULTSET as a table

Using a cursor to access data from a RESULTSET¶

To use a cursor to access the data from a RESULTSET, declare the cursor on the object. For example:

DECLARE
  ...
  res RESULTSET DEFAULT (SELECT col1 FROM mytable ORDER BY col1);
  c1 CURSOR FOR res;

Copy

When you declare a cursor on a RESULTSET, the cursor gets access to the data already in the RESULTSET. Executing the OPEN command on the cursor does not execute the query for the RESULTSET again.

You can then open the cursor and use the cursor to fetch the data.

Note

If the results include GEOGRAPHY values, you must cast the values to the GEOGRAPHY type before passing the values to any functions that expect GEOGRAPHY input values. See Using a cursor to retrieve a GEOGRAPHY value.

Returning a RESULTSET as a table¶

If you want to return the results that the RESULTSET points to, pass the RESULTSET to TABLE(...). For example:

CREATE PROCEDURE f()
  RETURNS TABLE(column_1 INTEGER, column_2 VARCHAR)
  ...
    RETURN TABLE(my_resultset_1);
  ...

Copy

This is similar to the way that TABLE(...) is used with table functions (such as RESULT_SCAN).

As shown in the example, if you write a stored procedure that returns a table, you must declare the stored procedure as returning a table.

Note

Currently, the TABLE(resultset_name) syntax is supported only in the RETURN statement.

Even if you have used a cursor to fetch rows from the RESULTSET, the table returned by TABLE(resultset_name) still contains all of the rows (not just the rows starting from the cursor’s internal row pointer).

Limitations of the RESULTSET data type¶

Although RESULTSET is a data type, Snowflake does not yet support:

Declaring a column of type RESULTSET.
Declaring a parameter of type RESULTSET.
Declaring a stored procedure’s return type as a RESULTSET.

Snowflake supports RESULTSET only inside Snowflake Scripting.

In addition, you can’t use a RESULTSET directly as a table. For example, the following is invalid:

SELECT * FROM my_result_set;

Copy

Examples of using a RESULTSET¶

The following sections provide examples of using a RESULTSET:

Setting up the data for the examples
Example: Returning a table from a stored procedure
Example: Constructing the SQL statement dynamically
Example: Declaring a RESULTSET variable without a DEFAULT clause
Example: Using a CURSOR with a RESULTSET
Example: Running child jobs that query tables concurrently
Example: Running child jobs that insert rows into tables concurrently
Additional examples that use a RESULTSET

Setting up the data for the examples¶

Many of the examples below use the table and data shown below:

CREATE OR REPLACE TABLE t001 (a INTEGER, b VARCHAR);
INSERT INTO t001 (a, b) VALUES
  (1, 'row1'),
  (2, 'row2');

Copy

Example: Returning a table from a stored procedure¶

The following code shows how to declare a RESULTSET and return the results that the RESULTSET points to. The RETURNS clause in the CREATE PROCEDURE command declares that the stored procedure returns a table, which contains one column of type INTEGER.

The RETURN statement inside the block uses the TABLE(...) syntax to return the results as a table.

Create the stored procedure:

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  BEGIN
    RETURN TABLE(res);
  END;

Copy

Note: If you are using 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 SnowSQL, the Classic Console, and Python Connector):

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET default (SELECT a FROM t001 ORDER BY a);
  BEGIN
      RETURN TABLE(res);
  END;
$$;

Copy

Call the stored procedure:

CALL test_sp();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

You can also use the RESULT_SCAN function to process the results of the stored procedure call:

SELECT *
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  ORDER BY 1;

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Example: Constructing the SQL statement dynamically¶

You can construct the SQL dynamically. The following is an example that executes the same query as the previous stored procedure but that uses a SQL statement that is constructed dynamically:

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
  RETURNS TABLE(a INTEGER)
  LANGUAGE SQL
AS
DECLARE
  res RESULTSET;
  query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
BEGIN
  res := (EXECUTE IMMEDIATE :query USING(table_name));
  RETURN TABLE(res);
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_dynamic(table_name VARCHAR)
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
    res RESULTSET;
    query VARCHAR DEFAULT 'SELECT a FROM IDENTIFIER(?) ORDER BY a;';
  BEGIN
    res := (EXECUTE IMMEDIATE :query USING(table_name));
    RETURN TABLE(res);
  END
$$
;

Copy

To run the example, call the stored procedure and pass in the table name:

CALL test_sp_dynamic('t001');

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Example: Declaring a RESULTSET variable without a DEFAULT clause¶

The following code shows how to declare a RESULTSET without a DEFAULT clause (i.e. without associating a query with the RESULTSET), and then associate the RESULTSET with a query later.

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
  DECLARE
    res RESULTSET;
  BEGIN
    res := (SELECT a FROM t001 ORDER BY a);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_02()
RETURNS TABLE(a INTEGER)
LANGUAGE SQL
AS
$$
  DECLARE
      res RESULTSET;
  BEGIN
      res := (SELECT a FROM t001 ORDER BY a);
      RETURN TABLE(res);
  END;
$$;

Copy

To run the example, call the stored procedure:

CALL test_sp_02();

Copy

+---+
| A |
|---|
| 1 |
| 2 |
+---+

Example: Using a CURSOR with a RESULTSET¶

The following code shows how to use a cursor to iterate over the rows in a RESULTSET:

Create the stored procedure:

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS VARCHAR
LANGUAGE SQL
AS

DECLARE
  accumulator INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
  cur1 CURSOR FOR res1;
BEGIN
  FOR row_variable IN cur1 DO
    accumulator := accumulator + row_variable.a;
  END FOR;
  RETURN accumulator::VARCHAR;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_03()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT (SELECT a FROM t001 ORDER BY a);
    cur1 CURSOR FOR res1;
  BEGIN
    FOR row_variable IN cur1 DO
        accumulator := accumulator + row_variable.a;
    END FOR;
    RETURN accumulator;
  END;
$$;

Copy

Call the stored procedure, and the results add the values for a in the table (1 + 2):

CALL test_sp_03();

Copy

+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+

Example: Running child jobs that query tables concurrently¶

The following code shows how to use the ASYNC keyword to run multiple child jobs that query tables concurrently.

This example uses the data in the following tables:

CREATE OR REPLACE TABLE orders_q1_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q1_2024 VALUES (1, 500.00);
INSERT INTO orders_q1_2024 VALUES (2, 225.00);
INSERT INTO orders_q1_2024 VALUES (3, 725.00);
INSERT INTO orders_q1_2024 VALUES (4, 150.00);
INSERT INTO orders_q1_2024 VALUES (5, 900.00);

CREATE OR REPLACE TABLE orders_q2_2024 (
  order_id INT,
  order_amount NUMBER(12,2));

INSERT INTO orders_q2_2024 VALUES (1, 100.00);
INSERT INTO orders_q2_2024 VALUES (2, 645.00);
INSERT INTO orders_q2_2024 VALUES (3, 275.00);
INSERT INTO orders_q2_2024 VALUES (4, 800.00);
INSERT INTO orders_q2_2024 VALUES (5, 250.00);

Copy

The following stored procedure performs the following actions:

Queries both tables for the order_amount values in all rows and returns the results to different RESULTSETs (one for each table).
Specifies that the queries run as concurrent child jobs by using the ASYNC keyword.
Executes the AWAIT statement for each RESULTSET so that the procedure waits for the queries to finish before proceeding. Query results for a RESULTSET can’t be accessed until AWAIT is run for the RESULTSET.
Uses a cursor to calculate the sum of the order_amount rows for each table.
Adds the totals for the tables and returns the value.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  accumulator1 INTEGER DEFAULT 0;
  accumulator2 INTEGER DEFAULT 0;
  res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
  res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
BEGIN
  AWAIT res1;
  LET cur1 CURSOR FOR res1;
  OPEN cur1;
  AWAIT res2;
  LET cur2 CURSOR FOR res2;
  OPEN cur2;
  FOR row_variable IN cur1 DO
      accumulator1 := accumulator1 + row_variable.order_amount;
  END FOR;
  FOR row_variable IN cur2 DO
      accumulator2 := accumulator2 + row_variable.order_amount;
  END FOR;
  RETURN accumulator1 + accumulator2;
END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_query()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    accumulator1 INTEGER DEFAULT 0;
    accumulator2 INTEGER DEFAULT 0;
    res1 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q1_2024);
    res2 RESULTSET DEFAULT ASYNC (SELECT order_amount FROM orders_q2_2024);
  BEGIN
    AWAIT res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;
    AWAIT res2;
    LET cur2 CURSOR FOR res2;
    OPEN cur2;
    FOR row_variable IN cur1 DO
        accumulator1 := accumulator1 + row_variable.order_amount;
    END FOR;
    FOR row_variable IN cur2 DO
        accumulator2 := accumulator2 + row_variable.order_amount;
    END FOR;
    RETURN accumulator1 + accumulator2;
  END;
$$;

Copy

Call the stored procedure:

CALL test_sp_async_child_jobs_query();

Copy

+--------------------------------+
| TEST_SP_ASYNC_CHILD_JOBS_QUERY |
|--------------------------------|
|                           4570 |
+--------------------------------+

Example: Running child jobs that insert rows into tables concurrently¶

The following code shows how to use the ASYNC keyword to run multiple child jobs that insert rows into a table concurrently.

The following stored procedure performs the following actions:

Creates the orders_q3_2024 table if it doesn’t exist.
Creates two RESULTSETs, insert_1 and insert_2, that hold the results of inserts into the table. The stored procedure arguments specify the values that are inserted into the table.
Specifies that the inserts run as concurrent child jobs by using the ASYNC keyword.
Executes the AWAIT statement for each RESULTSET so that the procedure waits for the inserts to finish before proceeding. The results of a RESULTSET can’t be accessed until AWAIT is run for the RESULTSET.
Creates a new RESULTSET res that holds the results of a query on the orders_q3_2024 table.
Returns the results of the query.

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;

Copy

CREATE OR REPLACE PROCEDURE test_sp_async_child_jobs_insert(
  arg1 INT,
  arg2 NUMBER(12,2),
  arg3 INT,
  arg4 NUMBER(12,2))
RETURNS TABLE()
LANGUAGE SQL
AS
$$
  BEGIN
   CREATE TABLE IF NOT EXISTS orders_q3_2024 (
      order_id INT,
      order_amount NUMBER(12,2));
    LET insert_1 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg1, :arg2);
    LET insert_2 RESULTSET := ASYNC (INSERT INTO orders_q3_2024 SELECT :arg3, :arg4);
    AWAIT insert_1;
    AWAIT insert_2;
    LET res RESULTSET := (SELECT * FROM orders_q3_2024 ORDER BY order_id);
    RETURN TABLE(res);
  END;
$$;

Copy

Call the stored procedure:

CALL test_sp_async_child_jobs_insert(1, 325, 2, 241);

Copy

+----------+--------------+
| ORDER_ID | ORDER_AMOUNT |
|----------+--------------|
|        1 |       325.00 |
|        2 |       241.00 |
+----------+--------------+

Additional examples that use a RESULTSET¶

Here are additional examples that use a RESULTSET:

Use a RESULTSET-based FOR loop

This example shows you how to use a FOR loop that iterates over a RESULTSET.
Return a table for a cursor

This example shows you how to use a cursor to return a table of data in a RESULTSET.
Update table data with user input

This example shows you how to use bind variables based on user input to update data in a table. It uses a FOR loop with conditional logic to iterate over the rows in a RESULTSET.
Filter and collect data

This example shows you how to use a RESULTSET to collect data and insert that data into a table to track historical trends.