Snowflake stored procedure argument is not recognized

Question

I'm constructing a snowflake stored procedure and I'm facing difficulty in using the passed argument in the snowflake procedure.

create or replace procedure dumper(n float)
 returns float
 language javascript
 execute as caller
 as 
 $$
    
    var text = "select file_name from table(information_schema.COPY_HISTORY(TABLE_NAME=> 'records', start_time=> dateadd(hours, ?, current_timestamp())));";
    var statement = snowflake.createStatement({sqlText: text, binds: [n]});
    var result = statement.execute();
    return statement.getRowCount();
 $$
 ;

attempting to call the above procedure

call dumper(-2);

result in the following error

JavaScript execution error: Uncaught ReferenceError: n is not defined in DUMPER at ' var statement = snowflake.createStatement({sqlText: text, binds: [n]});' position 70 stackstrace: DUMPER line: 4

I tried using the interpolation one discussed over here but that too had no success.

Any clue on how to work with passed argument.

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-12-21T16:12:04

You have to capitalize "N" in your JavaScript code:

var statement = snowflake.createStatement({sqlText: text, binds: [N]});

Variables passed into Snowflake stored procedures behave like other object names until they're inside the JavaScript. If they're not double quoted, then Snowflake implicitly capitalizes them. Remember to uppercase all parameters passed into SPs and UDFs in Snowflake. Variables defined inside the SP or UDF using JavaScript follow the normal rules for the language.

Since the regular rules apply to Snowflake identifiers as they do to variables passed into procedures and functions, you can double quote parameters if you want to use lower or mixed case variable names:

create or replace function echo_string("n" string)
returns string
language javascript
as
$$
    return n; // This works because "n" is double quoted in the signature
$$;

select echo_string('Hello world.');

Snowflake stored procedure argument is not recognized

1 Answers