09 Dec 19 · npack · #Snowflake ·   Bookmark   ×

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

npack

posted on 09 Dec 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied