Snowflake stored procedure variable binding error

Question

I am trying to create and execute a simple Snowflake stored procedure which takes an input argument and creates a stage. However, when I try to call the procedure it throws ERROR: invalid value [:] for parameter

create or replace procedure raw.test.create_stage_test(PARM_URL string)
    returns string
    language javascript
    execute as owner
    as
    $$
    var cmd = `create or replace stage raw.test.agency_type_test
               url =:1
               file_format = (type = json)
               credentials = (aws_role = 'arn:aws:iam::myrole');`
               
    var sql_statement = snowflake.createStatement({
     sqlText: cmd,
     binds: [PARM_URL]
     });
    
    try {
    var rs = sql_statement.execute();
     rs.next()
     rcount = rs.getColumnValue(1);
       if (rcount == 0){
        throw "row count is 0";
       }
     return "Rows count: " + rcount;
    }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$;


CALL raw.test.create_stage_test('s3://mybucket');

Mike Donovan Mike Donovan · Accepted Answer · 2020-02-23T19:48:59

Perhaps using interpolation, as in the following code snippet, could be an alternative? Note the single quotes included around the url value:

var cmd = `create or replace stage agency_type_test
           url='${PARM_URL}'
           file_format = (type = json)
           credentials = (aws_role = 'arn:aws:iam::myrole');`

var sql_statement = snowflake.createStatement({
 sqlText: cmd
 });

Snowflake stored procedure variable binding error

1 Answers