How to get Rowcount or Activitycount inside Snowflake Stored procedure

Stored procedures have complex processing logic. We can put a bunch of sql or procedural commands in a stored procedure and schedule it to run whenever required. While doing the DML operations, you might want to find the number of rows inserted, updated or deleted by your sql query. Since, stored procedures are written in javascript it could be a little tricky, but its achievable.

Here's a simple procedure that does a merge and captures & returns the number of rows insert and rows update:

CREATE OR REPLACE PROCEDURE utl.arch_merge_sp(P_STAGE_TBL VARCHAR, P_FINAL_TBL VARCHAR)
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS CALLER
AS $$
  var sqlCmd = "";
  var sqlStmt = "";
  var result = "";
  try {
    sqlCmd = `
      MERGE INTO final_t F USING stage_t S
      ON F.KEY_ID = S.KEY_ID
      WHEN MATCHED THEN UPDATE SET
       F.ATTR_NM = S.ATTR_NM
      ,F.ATTR_NBR = S.ATTR_NBR
      WHEN NOT MATCHED THEN INSERT (
       F.KEY_ID
      ,F.ATTR_NM
      ,F.ATTR_NBR
      ) VALUES (
       S.KEY_ID
      ,S.ATTR_NM
      ,S.ATTR_NBR);
      `;
    sqlStmt = snowflake.createStatement( {sqlText: sqlCmd} );
    rs = sqlStmt.execute();
    sqlCmd = 
      `SELECT "number of rows inserted", "number of rows updated"
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))`;
    sqlStmt = snowflake.createStatement( {sqlText: sqlCmd} );
    rs = sqlStmt.execute();
    rs.next();
    result += "Rows inserted: " + rs.getColumnValue(1) + ", Rows updated: " + rs.getColumnValue(2)
  }
  catch (err) {
    result =  "Failed: Code: " + err.code + " | State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt; 
    }
  return result;
$$;

nVector

posted on 09 Dec 19