How can I store output of an sql query into an array in a stored procedure in Snowflake?

user_17579071 0
Viewed 521

I am creating a snowflake stored procedure where I need to store the output if the SQL query into an array , whose values will be later used to create SQL Command which will be the output. Please suggest how do I store the output in an array.

snowflake-cloud-data-platform arrays
1 Answers

Please refer to the doc on how to setup SP in Snowflake:

https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html

There are lots of examples over there.

You just need to know how to query Snowflake and get results, and the rest are just standard Javascript.

Example as below:

create or replace procedure test_sp()
returns string
language javascript
as 
$$
    var my_data = [];
    var stmt = snowflake.createStatement({sqlText: "SELECT * FROM table_name" });
    var rs = stmt.execute();
    while (rs.next()) {
        
        my_data.push(rs.getColumnValue(1));
        my_data.push(rs.getColumnValue(2));
    }

    // then you can use my_data array in later part of your code
    
    return '';
$$;
user_98793790
Related