snowflake-cloud-data-platform - Snowflake 将参数传递给多个存储过程

Question

我已经创建了单独的过程，当手动运行时使用 2 个日期参数运行。但是我正在尝试编写一个包装程序来顺序调用这些过程。到目前为止我所做的示例代码如下。

CREATE OR REPLACE PROCEDURE SANDBOX.MERC.SP_LOAD_INCR_MERGE_DT("STARTDATE" DATE, "ENDDATE" DATE)
RETURNS VARCHAR(1000000)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '

var stmt = snowflake.createStatement({sqlText: `call SANDBOX.MERC.SP_LOAD_HDR_INCR_MERGE_DT(?, ?)`,binds: [STARTDATE, ENDDATE]});
var result = stmt.execute();


var stmt = snowflake.createStatement({sqlText: `call SANDBOX.MERC.SP_LOAD_LINEDETAIL_INCR_MERGE_DT(?, ?)`,binds: [STARTDATE, ENDDATE]});
var result = stmt.execute();


var stmt = snowflake.createStatement({sqlText: `call SANDBOX.MERC.header_daily(?, ?)`,binds: [STARTDATE, ENDDATE]});
var result = stmt.execute();

var stmt = snowflake.createStatement({sqlText: `call SANDBOX.MERC.lineitem_daily(?, ?)`,binds: [STARTDATE, ENDDATE]});
var result = stmt.execute();

return sql_command;';

任何建议都会很棒。我确实已经看过这些网址 https://community.snowflake.com/s/question/0D50Z00009E2qFHSAZ/how-do-i-bind-a-date-within-a-stored-procedure

通过

单独调用这些过程

call SANDBOX.MERC.SP_LOAD_HDR_INCR_MERGE_DT ('2021-01-12','2021-01-14' )

按预期工作。提前感谢您的帮助。

Answer 1

您遇到的问题是将日期转换为字符串，要以 Snowflake 喜欢的方式进行转换，您可以对变量使用 .toISOString() 函数。

下面是完整的工作示例。

create table stacko_69408091 (
    dt1     DATE,
    dt2     DATE);


CREATE OR REPLACE PROCEDURE sp_insert(p1 date, p2 date)
RETURNS ARRAY
LANGUAGE javascript
EXECUTE AS caller
AS 
$$
try {
    var whereAmI = 1;
    var return_array = [];
    var counter = 0;

    var p1_str = "p1: " + P1.toString();
    var p2_str = "p2: " + P2.toString();
    return_array.push(p1_str)
    return_array.push(p2_str)

    var whereAmI = 2;
    var sqlquery = `
        INSERT INTO stacko_69408091 VALUES (?, ?)
    `;

    snowflake.execute({"sqlText" : sqlquery, "binds" : [P1.toISOString(), P2.toISOString()]});

    var whereAmI = 3;
    var query_id_list = snowflake.execute({sqlText: `SELECT LAST_QUERY_ID()::varchar;`});
    if (query_id_list.next()){
      var query_id = query_id_list.getColumnValue(1);
      return_array.push(query_id);
    }
  
    var whereAmI = 4;
    return_array.push("end process");

    return return_array;
    }

catch (err) {
   return_array.push("error found");
   return_array.push("whereAmI: " + whereAmI);
   return_array.push("err.code: " + err.code);
   return_array.push("err.state: " + err.state);
   return_array.push("err.message: " + err.message);
   return_array.push("err.stacktracetxt: " + err.stacktracetxt);
   return return_array;
}

$$;

CALL sp_insert('2021-09-01'::date, '2021-10-01'::date);
select * from stacko_69408091;




CREATE OR REPLACE PROCEDURE sp_main(STARTDATE DATE, ENDDATE DATE)
RETURNS VARCHAR(1000000)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$

var stmt = snowflake.createStatement({sqlText: `call sp_insert(?, ?)`,binds: [STARTDATE.toISOString(), ENDDATE.toISOString()]});
var result = stmt.execute();


var stmt = snowflake.createStatement({sqlText: `call sp_insert(?, ?)`,binds: [STARTDATE.toISOString(), ENDDATE.toISOString()]});
var result = stmt.execute();


return "done";

$$
;


CALL sp_main('2021-09-01'::date, '2021-10-01'::date);
select * from stacko_69408091;