Snowflake only executes first SQL command in stored procedure

Question

I was trying to create a procedure that copies the content of my table into S3 partitioned by 2 different combinations. For that I did the following:

  $$
  var cmd_partition1 = `...`
  var cmd_partition2 = `...`
  var store_data_partitioned_by_1_command = snowflake.createStatement({ sqlText: cmd_partition1 })
  var store_data_partitioned_by_2_command = snowflake.createStatement({ sqlText: cmd_partition2 })

  try {
      store_data_partitioned_by_1_command.execute()
      store_data_partitioned_by_2_command.execute()
      return 'Succeeded.'
  }
  catch (err) {
      return "Failed: " + err
  }
  $$;

However, each time I execute the procedure the partitioning is only performed for the 1st combination, while the 2nd one is ignored.

Do you know why this is happening and how can I solve it?

I tested each one of the cmd_partition (1 and 2) in the Snowflake GUI and both of them work as expected.

Answer 1

create table test_sp(id int);

-- test sql is good
insert into test_sp values (1); 
insert into test_sp values (2);

-- clean up
truncate table test_sp; 

create or replace procedure double_exec()
returns varchar
language javascript as 
 $$
  var cmd_partition1 = `insert into test_sp values (1)`
  var cmd_partition2 = `insert into test_sp values (2)`
  var store_data_partitioned_by_1_command = snowflake.createStatement({ sqlText: cmd_partition1 })
  var store_data_partitioned_by_2_command = snowflake.createStatement({ sqlText: cmd_partition2 })

  try {
      store_data_partitioned_by_1_command.execute()
      store_data_partitioned_by_2_command.execute()
      return 'Succeeded.'
  }
  catch (err) {
      return "Failed: " + err
  }
  $$;

and now to run

call double_exec();

DOUBLE_EXEC
Succeeded.

so lets check the steps ran

select * from test_sp;

ID
1
2

so the concept of having to executions in a row is valid.

which makes it something about the SQL itself, and not the stored procedure.