Snowflake Scripting Cursor Syntax and Examples

In my other article, we have discussed Snowflake scripting control structures and loops to manipulate Snowflake data. In this article, let us discuss how to define and use the Snowflake scripting cursor within a stored procedure and anonymous block. Snowflake also support JavaScript to write stored procedures, but it does not support cursor when JavaScript used. You can use the JavaScript result as a cursor alternative.

Page Content

• Introduction
• Understanding Cursors in Snowflake
• How use Cursors in Snowflake Scripting?
  • Declaring Cursors
  • Opening Cursors
  • Fetching Data from Cursors
  • Closing Cursors
• Snowflake Cursor Examples
  • Snowflake Anonymous Block with Cursor
  • Snowflake Stored Procedure with Cursor
  • Returning a Table for a Snowflake Cursor
• Best Practices and Performance Considerations
• Conclusion

Introduction

In a Snowflake, cursor serves as a mechanism for iterating through rows of a result set, enabling procedural logic within SQL scripts. Whether it’s processing large datasets, performing complex calculations, or executing conditional operations, cursors offer a flexible and dynamic approach. In this post, we will look into the overview of Snowflake’s cursor syntax and demonstrate practical examples of how they can be used effectively. So, let’s embark on a journey through the world of Snowflake cursors and unlock the full potential of your data handling capabilities. Cursors are really important when you are migrating to Snowflake from other databases

Cursor in Snowflake Scripting

A cursor in SQL is a temporary space created in system memory when a SQL SELECT statement is executed. SQL cursor is a set of rows along with an internal pointer that identifies a current row. To retrieve data from the results of a query, use a cursor. You can use a cursor in loops to iterate over the rows in the results.

The SQL programming language such as PL/SQL, pgPL/SQL, etc. extensively uses cursor to loop through the result sets. Until recently Snowflake was supporting only JavaScript, but now they started supporting scripting much similar to PL/SQL.

How use Cursors in Snowflake Scripting?

Using cursors in your Snowflake scripting stored procedure is pretty straight forward. Following steps guides you on how to use cursor in Snowflake scripting.

Declare Snowflake Cursor

You can declare a cursor in the DECLARE section of a block or in the BEGIN … END section of the block within your Snowflake script.

For example, to declare a cursor for a query:

DECLARE
  c1 CURSOR FOR SELECT column1 FROM table;

To declare a cursor for a RESULTSET:

DECLARE

  res RESULTSET DEFAULT (SELECT column1 FROM table);
  c1 CURSOR FOR res;

You can also declare cursor within BEGIN and END blocks.

BEGIN

  LET c1 CURSOR FOR SELECT price FROM invoices;

Use LET to declare cursor withing BEGIN and END blocks.

Open Snowflake Cursor

Execute the OPEN command to open a cursor in your Snowflake script. The DECLARE statement declares a cursor and define query associated with that cursor, the query is not executed until you open the cursor by executing the OPEN command.

OPEN c1;

If your query contains any bind parameters (? characters), add a USING clause to specify the list of variables to bind to those parameters.

LET c1 CURSOR FOR SELECT col1 FROM table WHERE col2 > ? and col2 < ?;
OPEN c1 USING (minimum_price, maximum_price);

Fetch Rows from Snowflake Cursor

Execute the FETCH command to fetch one or more rows. The FETCH command retrieve the current row from the result set and advance the internal current row pointer to point to the next row in the result set.

FETCH c1 INTO var_for_column_value;

Close Snowflake Cursor

Finally, close cursor using CLOSE command.

CLOSE c1;

Snowflake Cursor Examples

Snowflake Anonymous Block with Cursor

Following anonymous block example demonstrate all above mentioned steps to use cursors in Snowflake scripting.

execute immediate $$
declare
  id integer default 0;
  c1 cursor for select id from invoices where price > 10 and price < 33.33;
begin
  open c1;
  fetch c1 into id;
  close c1;
  return id;
end;
$$;

Snowflake Stored Procedure with Cursor

You can also create a stored procedure for above anonymous block using Snowflake scripting.

create or replace procedure cursor_demo()
returns integer
language sql 
as
$$
declare
  id integer default 0;
  c1 cursor for select id from invoices where price > 10 and price < 33.33;
begin
  open c1;
  fetch c1 into id;
  close c1;
  return id;
end;
$$;

Returning a Table for a Snowflake Cursor

If you need to return a table of data from a cursor, you can pass the cursor to RESULTSET_FROM_CURSOR(cursor), which in turn you can pass to TABLE(...).

DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;

Best Practices and Performance Considerations

Best practices

• Use cursors only when necessary. Cursors can be slow and resource-intensive, so they should be used when necessary.
• Use cursors to iterate over a small number of rows. Cursors are not well-suited for iterating over large result sets.
• Close the cursor when you are finished with it. This frees up resources and prevents the cursor from being left open.

Performance Considerations

• Use a WHERE clause to filter the cursor result set. This will reduce the number of rows that the cursor needs to iterate over.
• Use a bind variable to pass a parameter to the cursor query. This will help the database to optimize the query execution.
• Avoid opening and closing the cursor multiple times. If you need to iterate over the cursor result set multiple times, you can use a loop instead of opening and closing the cursor each time.
• Use a temporary table to store the cursor result set. This can be more efficient than storing the result set in memory.

Conclusion

Snowflake cursors are a powerful features for iterating over result sets in Snowflake. They can be used to perform a variety of tasks, such as processing data row by row, updating data in batches, and performing complex calculations. However, cursors can also be slow and resource-intensive, so it is important to use them wisely.

In this blog post, we discussed the basic syntax of Snowflake cursors and provided some tips for using them efficiently and effectively.

Related Articles,

• Rows Affected by Last Snowflake SQL Query – Example

Hope this helps 🙂