雪花存储过程变量绑定错误 [英] Snowflake stored procedure variable binding error

问题描述

我正在尝试创建并执行一个简单的Snowflake存储过程，该过程接受一个输入参数并创建一个Stage。但是，当我尝试调用它抛出的过程时 错误：参数的值[：]无效

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');

推荐答案

也许可以使用插值(如下面的代码片段所示)作为替代方案？请注意url值两边的单引号：

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
 });

这篇关于雪花存储过程变量绑定错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！