Snowflake Stored Procedure - input paramters "undefined"

Anton S. 0
Viewed 24

I am trying to create a stored procedure in snowflake for generating snow pipes, i have the sql needed, the issue I'm having is now wrappping that sql in a procedure we can call and pass in values like the db name and table name.

when I try to pass these values into the procedure I get an erorr saying "Uncaught ReferenceError: db_name is not defined in EXECUTE_SQL_COMMAND"

code:

create or replace procedure execute_sql_command(db_name varchar,tbl_name varchar)
returns string
language javascript
as 
$$
    var target_database = db_name;
    var target_table = tbl_name;
    var sql_string = 
        "select concat(rtrim('CREATE OR REPLACE PIPE ','" + target_database + "'));";
    
    var res = snowflake.createStatement({sqlText: sql_string});
    var ex = res.execute();
    ex.next();
    
    var query_value = ex.getColumnValue(1);
    return query_value.toString();
$$;


call execute_sql_command('dev_stage_db','miattr');
snowflake-cloud-data-platform
2 Answers

Case-sensitivity in JavaScript Arguments

Argument names are case-insensitive in the SQL portion of the stored procedure code, but are case-sensitive in the JavaScript portion.

For stored procedures (and UDFs) that use JavaScript, identifiers (such as argument names) in the SQL portion of the statement are converted to uppercase automatically (unless you delimit the identifier with double quotes), while argument names in the JavaScript portion will be left in their original case. This can cause your stored procedure to fail without returning an explicit error message because the arguments aren’t seen.

Using uppercase identifiers (especially argument names) consistently across your SQL statements and JavaScript code tends to reduce silent errors.

Thus:

create or replace procedure execute_sql_command(db_name varchar,tbl_name varchar)
returns string
language javascript
as 
$$
    var target_database = DB_NAME;
    var target_table = TBL_NAME;
    // ....
$$;
Lukasz Szozda0

So what I've found - the input parameters need to be wrapped in "" example:

create or replace procedure execute_sql_command("db_name" varchar,"tbl_name" varchar)
Anton S.0
Related