Implicit Cursor attribute helper

An implicit cursor has attributes that return information about the most recently run SELECT or DML statement. This section explains how is this being emulated using Javascript.

Overview

These are the attributes that you can use inside Snowflake stored procedures using this helper:

• FOUND

• NOTFOUND

• ROWCOUNT

• ISOPEN

In Snowflake code, inside the procedures, you will find the initialization of these attributes:

 var SQL = {
  FOUND : false,
  NOTFOUND : false,
  ROWCOUNT : 0,
  ISOPEN : false
 };

The values of the attributes: FOUND, NOTFOUND, ROWCOUNT are updated inside the EXEC helper.

The attribute ISOPEN is always false, just like in Oracle.

Usage Samples

Input

CREATE OR REPLACE PROCEDURE PROC1
IS
VAR1 VARCHAR(100) := '';
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 WHERE COL1 = 1;
    VAR1 := 'Rows affected: ' || TO_CHAR(SQL%ROWCOUNT);
    VAR1 := 'Error: ' || SQLERRM;

    PKG.TEST_PROC1(SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND);
    PKG.TEST_PROC2(SQLCODE);

    SELECT SQL%ROWCOUNT FROM DUAL;
END;

Output

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   var SQL = {
      FOUND : false,
      NOTFOUND : false,
      ROWCOUNT : 0,
      ISOPEN : false
   };
   var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
   // ... rest of the helpers
   // END REGION

   let VAR1 = undefined;
   
   [VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE1 WHERE COL1 = 1`);
   
   // ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
   VAR1 = `Rows affected: ${concatValue((EXEC(`SELECT TO_CHAR(?)`,[SQL.ROWCOUNT]))[0])}`;
   // ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
   VAR1 = `Error: ${concatValue(SQLERRM)}`;
   
   EXEC(`CALL PKG.TEST_PROC1(?, ?, ?)`,[SQL.ROWCOUNT,SQL.FOUND,SQL.NOTFOUND]);
   EXEC(`CALL PKG.TEST_PROC2(?)`,[SQLCODE]);
   
   EXEC(`SELECT ? FROM DUAL`,[SQL.ROWCOUNT]);
$$;

SQLCODE and SQLERRM are converted into helper variables with the same name, and are binded in the same way as the cursor variables.