CODEWITHSUNDEEP
snowflake-cloud-data-platform
Scott Wood
Scott Wood

Reputation: 1127

How do I work with variables in snowflake

I set this up a while ago in something that is not really used. I was under the impression that it worked at the time, but I'm trying to test it now and am getting some errors.

Here is my snowflake code:

var rs = snowflake.createStatement( { sqlText: "select count(*) from toptal.stage_resellers" } ).execute();
rs.next();
var resellers = rs.getColumnValue(1);

I that that var resellers was going to define a variable that would have the number of rows in stage_resellers, but I'm not confident of that.

I decided to test it just by inserting the value into a logging table that I am using into an unused column for the time being.

Here is that code:

var stmt1 = snowflake.execute ( { sqlText:`insert into toptal.processing_executions values ('merge into dim_resellers', current_timestamp, 'processing', :resellers);`});

I'm getting this error:

Execution error in store procedure PROCESSING: SQL compilation error: error line 1 at position 110 Bind variable :resellers not set. At Snowflake.execute, line 47 position 24

I tried futzing around with setting the variable, to no avail.

I have a feeling that I am mixing up environments here, but I'm not sure what's going on.

Upvotes: 0

Views: 250

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

To execute:

var stmt1 = snowflake.execute ( { sqlText:`insert into toptal.processing_executions values ('merge into dim_resellers', current_timestamp, 'processing', :resellers);`});

variables has to be provided as ? or :1. Code becomes:

var stmt1 = snowflake.execute({
      sqlText: `insert into toptal.processing_executions
                values ('merge into dim_resellers', current_timestamp,
                        'processing', ?)`
     ,binds: [resellers] } );

More at documentation: Binding Variables

Upvotes: 1

Related Questions