Getting a count of rows inserted in a Snowflake procedure

Question

I'm using a procedure to insert rows into a table. I want to return the count of rows inserted, but I can't figure out how to turn on the variable substitution from inside a procedure. I've tried all of the following, but they all return errors:

snowflake.execute({sqlText: '!set variable_substitution=True'});
snowflake.execute({sqlText: 'set variable_substitution=True'});
snowflake.execute({sqlText: '-o variable_substitution=True'});

How do I turn this option on so I can run "select &__rowcount;" and get my count back?

Here's code for a test procedure, if that helps:

CREATE OR REPLACE PROCEDURE TEST_OF_GETTING_COUNTS()
  RETURNS VARCHAR
  LANGUAGE javascript
  CALLED ON NULL INPUT
  AS
  $$  
    // Turn on variable substitution
    snowflake.execute(
      {sqlText: '!set variable_substitution=True'}
    );
    
    // Prepare SQL to identify tables to be updated
    snowflake.execute({sqlText: 'SELECT 1'});
    
    // Now get the count of rows selected  
    var getCount = snowflake.createStatement({sqlText: "SELECT &__ROWCOUNT;"});
    var rowCountResultSet = getCount.execute();
    while (rowCount.next()) {
      rowCount= rowCountResultSet.getColumnValue(1);
    }

    // Turn off variable substitution
    snowflake.execute({sqlText: '!set variable_substitution=False'});
          
    return rowCount;
  $$;

CALL TEST_OF_GETTING_COUNTS();

You seem to be using a lot of SnowSQL-specific commands inside a javascript stored procedure, for some reason? Also, your SP is returning a varchar while a row count is an integer. This part of the documentation shows all the functions, with examples, that you will need to achieve what you want: https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html — NickW, May 06 '21 at 20:20
Thank you for the VERY useful link. This is only the second procedure I've written in Snowflake, and I had to learn JavaScript to do it. The getRowCount() function gives me exactly what I need! — ASturt, May 07 '21 at 22:55

score 0 · Answer 1 · answered May 07 '21 at 22:56

NickW, in his comment above, gave me a link to a page in Snowflake's documentation that lists all the JavaScript methods available for the snowflake, Statement, ResultSet, and SfDate objects. It gave me exactly what I needed: the getRowCount() method.

Getting a count of rows inserted in a Snowflake procedure

1 Answers1