Working with RESULTSETs

This topic explains how to use RESULTSETs 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.

Note that if you have a cursor and you need to return a table from the Snowflake Scripting block, 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> := ( <query> ) ;
Copy

Where:

resultset_name

The name to give the RESULTSET.

The name should be unique within the current scope.

The name must follow the naming rules for Object Identifiers.

query

The query to assign to the RESULTSET.

For example:

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

If you need to build a string of SQL 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

Note although you can set query to an EXECUTE IMMEDIATE statement for a RESULTSET, you cannot 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

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

Note that 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.

Note that 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 cannot 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

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

CREATE 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 or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

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();
+---+
| A |
|---|
| 1 |
| 2 |
+---+
Copy

Note that you can 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;
+---+
| A |
|---|
| 1 |
| 2 |
+---+
Copy

Example: Constructing the SQL Statement Dynamically

You can construct the SQL dynamically. The following is an example that executes the same query as the stored procedure above 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 ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;
Copy

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

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 ' || :table_name || ' ORDER BY a';
  BEGIN
    res := (EXECUTE IMMEDIATE :query);
    RETURN TABLE (res);
  END;
$$
;
Copy

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

call test_sp_dynamic('t001');

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

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

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

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

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

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

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:

CALL test_sp_03();
+------------+
| TEST_SP_03 |
|------------|
| 3          |
+------------+
Copy