Working with RESULTSETs¶

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> := ( <query> ) ;

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.

query

The query to assign to the RESULTSET.

For example:

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

If you need 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;

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.

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

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

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

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;

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

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

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;

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

CALL test_sp();

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

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

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

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;

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

CALL test_sp_dynamic('t001');

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

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;

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

CALL test_sp_02();

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

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;

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

CALL test_sp_03();

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