Handle Cursor in Snowflake Stored Procedures

Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic’s. Stored procedure also handles an exceptions if any in your data or custom exception handling. The relational databases such as Oracle, Redshift, Netezza, etc. supports cursor variables. In this article, we will check how to handle cursor variable in Snowflake stored procedures with an example.

In a relational database, cursors are extensively used in stored procedures to loop through the records from SELECT statements. Stored procedures encapsulate the business logic. For example, you can create a stored procedure to clean up the backup tables after every ETL jobs.

Stored Procedures in Snowflake

Snowflake uses JavaScript as a procedural language. It provides the control structures – branching and looping. Snowflake started supporting scripting to write stored procedures.

You can execute SQL statements such as dynamic SQL queries by calling functions in a JavaScript API. You should have basic knowledge about the JavaScript API to work with stored procedures in Snowflake.

Cursor Variables in Snowflake Scripting

Snowflake started supporting scripting recently. Now, you can write cursor using Snowflake script which is similar to Oracle PLSQL.

Cursor variable declaration is similar to other databases such as Oracle, Teradata, Redshift, etc.

For example,

create or replace procedure test_db.snowpark.v_test_prc(pv_cursor_limit number)
  returns int
  language sql
  EXECUTE AS CALLER
  as
  $$
  DECLARE
        r1 string;
        r2 string;
        cnt int:=0;
      lcur_dim_tstg  CURSOR  for
            SELECT ID, NAME FROM TEST_DB.SNOWPARK.v_test_stg;
BEGIN
	  for record in lcur_dim_tstg
      do
      begin
       r1:=record.id;
       r2:=record.name;
        insert into test_db.snowpark.v_test_ld 
        values (:r1, :r2);
        exception 
          when other then 
          insert into TEST_DB.SNOWPARK.v_test_err values (1, 'error');              
      end;
      end for;
end;
$$ 
;

Cursor Variable Equivalent in Snowflake JavaScript Stored Procedures

Since stored procedures use JavaScript, you can use resultset object as an alternative to a relational database cursor variable.

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a SQL statement that queries the database table(s).

You can use the resultset in your loop statements to loop through the records.

For example, consider following stored procedure that uses resultset to loop through results and return concatenated results.

create or replace procedure get_results()
    returns varchar not null
    language javascript
    as
    $$
	var return_value = "";
	var SQL_STMT = "select * from test1" ;
    var stmt = snowflake.createStatement(
           {
           sqlText: SQL_STMT
           }
        );
	/* Creates result set */
	var res = stmt.execute();
	
    while (res.next())  {
        return_value += "\n";
        return_value += res.getColumnValue(1);
        return_value += ", " + res.getColumnValue(2);
		return_value += ", " + res.getColumnValue(3);
        }
	return return_value;
	$$
	;

Execute this procedure using CALL command.

call get_results();

+-------------+
| GET_RESULTS |
|-------------|
|             |
| 1, 1, 1     |
| 2, 2, 2     |
| 3, 3, 3     |
| 4, 4, 4     |
+-------------+

Return Column Count of Snowflake Stored Procedure ResultSet

You can use getColumnCount() method of a prepared statement to return the count of columns in the resultSet.

For example,

var query = `SELECT * FROM table1`
var stmt = snowflake.createStatement({sqlText: query});
var result = stmt.execute();
var col_count = stmt.getColumnCount();

002140 (42601): SQL compilation error: Unknown function Error in Snowflake Stored procedure

This error is may be due to role issue. Please check the role you used to create the stored procedure and use same role to execute SP. Alternatively, grant privileges to the procedure to execute it.