I’m relatively new to Snowflake and currently working with stored procedures in SQL. In my procedures, I dynamically execute insert and update queries using EXECUTE IMMEDIATE :InsertSQL. To capture the number of rows inserted, I’ve been using SELECT $1 INTO insertcount FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())); after each dynamic execution.

However, I’m curious if there’s a more efficient or best practice approach to achieve the same result without relying on RESULT_SCAN(LAST_QUERY_ID()). I’ve heard about using cursors as an alternative,

LET Res RESULTSET;
Res := (EXECUTE IMMEDIATE :Insert_SQL);
LET cur CURSOR FOR Res;
OPEN cur;
FETCH cur INTO Insertcount;
CLOSE cur;

but I’m unsure about the trade-offs and which method is preferable in terms of performance and maintainability.