Snowflake Syntax when passing array into insert query

Question

My code obtains the column list of a table I have created the attributes of this table are the contextual values of a session in snowflake such as USER,DATABASE,WAREHOUSE...ETC Afterwards it places those attribute names into an array I then try to call on these names in making an insert query and this is where I am struggling with the syntax

Because each value in my array is USER, DATABASE,WAREHOUSE I am trying to call on the context functions like CURRENT_WAREHOUSE() Can someone please help me with the syntax

    for(i=0;i<arr.length;i++){
        v_sqlCode = `INSERT INTO SESSION_ATTRIBUTES( arr[i] )
                        "VALUES ("CALL CURRENT_"+arr[i]+"()")';    
    }

Greg Pavlik · Accepted Answer · 2021-09-14 17:35:27Z

1

You can't directly use the output of a Snowflake stored procedure programmatically. If you need to use the output value, you have to collect it using the RESULT_SCAN table function. That can get a bit complex to handle, directly in code, so it's far better to place it into a helper function. See this example of one SP calling another one and using its output value. It does this by calling the SP using the callSP helper function. Use the entire SQL statement including the call command into the SQL parameter for that function. It will run the SP, grab the result from the result_scan table function and return it.

create or replace procedure HELLO_WORLD()
returns string
language javascript
as
$$
    return "Hello, world.";
$$;

create or replace procedure CALL_HELLO_WORLD()
returns string
language javascript
execute as caller
as
$$

return callSP(`call HELLO_WORLD()`);

// ---- Main function above, helper functions below.

function callSP(sql){
    let cmd = {sqlText: sql};
    let stmt = snowflake.createStatement(cmd);
    stmt.execute();
    let result_scan  = `select $1 from table(result_scan(last_query_id()));`;
    let result_cmd   = {sqlText: result_scan};
    let result_stmt  = snowflake.createStatement(result_cmd);
    let rs           = stmt.execute();
    if(rs.next()) {
        return rs.getColumnValue(1);
    } else {
        return null;
    }
}

$$;

call call_hello_world();

edited Sep 14, 2021 at 17:35

answered Sep 14, 2021 at 15:02

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RMC_DEV Over a year ago

Hello Greg, first of all thanks for always helping me Secondly, in my array there are no values but just strings I am trying to mimic the "CALL CURRENT_DATABASE()" for example which will then get the current database and store it into my sessions table

Greg Pavlik Over a year ago

I updated my answer. You can't directly read the output of a Snowflake stored procedure. You have to do it the way the example callSP function shows. You can cut & paste that into the bottom of your stored procedure and use it.

Collectives™ on Stack Overflow

Snowflake Syntax when passing array into insert query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related