Snowflake passing parameters to multipule stored procedures

user_3499391 0
Viewed 95

I have created individual procs that when ran manually run with 2 date params. BUT I'm trying to write a wrapper proc that calls these procs sequentially. What I have done so far sample code below.

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

Any advice would be greatful. I did already look at these urls https://community.snowflake.com/s/question/0D50Z00009E2qFHSAZ/how-do-i-bind-a-date-within-a-stored-procedure

Individual calls to these procs via

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

WORKS as expected. Thanks for any help in advance.

snowflake-cloud-data-platform
1 Answers

The issue you are having is the conversion of dates to strings, and to do so in a fashion Snowflake likes, you can use the .toISOString() function on your variables.

fully working example below.

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;
user_87675700
Related