return sql count in javascript Stored procedure is returning NULL

user_5862231 0
Viewed 113

I am trying to return sql count using javascript function but the below code is always returning NULL. However, the standalone sql is running fine. Any clue where I am getting this wrong? +++++++++++++++++++++++++++++++++

CREATE OR REPLACE PROCEDURE TO_STG()
returns varchar
LANGUAGE JAVASCRIPT
AS
$$
var sql_cmd = 'SELECT COUNT(*) FROM AVAILABLE'
var SqlCOUNT = snowflake.createStatement({
    sqlText: sql_cmd
});

try {
    var sql_Count = SqlCOUNT.execute();
    var Count = sql_Count.getColumnValue();
    return Count
} catch (err) {
    result = "Failed: Code: " + err.code
}
$$;
snowflake-cloud-data-platform
1 Answers

You have to advance your cursor in the ResultSet before attempting to read from it. Since the count statement will return only one row, you don't need a loop. You only need to call the ResultSet .next() method once. You also need to specify which column to read. Since there's only one column, you can refer to it using the numeral 1, but if you have several use aliases if necessary and refer to them in getColumnValue using the column name.

CREATE OR REPLACE PROCEDURE TO_STG()
returns varchar
LANGUAGE JAVASCRIPT
AS
$$
var sql_cmd = 'SELECT COUNT(*) FROM AVAILABLE'
var SqlCOUNT = snowflake.createStatement({
    sqlText: sql_cmd
});

try {
    var sql_Count = SqlCOUNT.execute();
    sql_Count.next();
    var Count = sql_Count.getColumnValue(1);
    return Count
} catch (err) {
    result = "Failed: Code: " + err.code
}
$$;
user_127563810
Related