CODEWITHSUNDEEP
snowflake-cloud-data-platform
Dharaneswara Reddem
Dharaneswara Reddem

Reputation: 1

Snowflake - passing a dynamic value to stored procedure in call statement w

var P_ENV = 'int' var p_sqlText: CALL LS_**${P_ENV}**.HIID_SECURED.sp_getStatCertSaltValue(:1,:2); var stmt = snowflake.createStatement({sqlText:p_sqlText,binds: [P_ENV,P_STARTCERTID] });

    var rs = stmt.execute();
    rs.next();
    var p_saltvalue= rs.getColumnValue(1);

Unable to execute these lines in an stored procedure - calling the stored procedure with in the stored procedure - Thoughts please

Upvotes: 0

Views: 131

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

Can you share the error message you get?

When I tried to test it, I was able to call the procedure:

create or replace procedure int_sp_getStatCertSaltValue( v1 varchar, v2 varchar)
returns object
language javascript
as
$$
  return { "v1": V1, "v2": V2 };
$$;

create or replace procedure spinsp()
returns object
language javascript
as
$$
 var P_ENV = 'int';
 var P_STARTCERTID = 1
 var p_sqlText = `CALL ${P_ENV}_sp_getStatCertSaltValue(:1,:2)`; 
 var stmt = snowflake.createStatement({sqlText:p_sqlText,binds: [P_ENV,P_STARTCERTID] });
 var res = stmt.execute();
 res.next();
 return res.getColumnValue(1);
$$
;

call spinsp();


+----------------------------+
|           SPINSP           |
+----------------------------+
| { "v1": "int", "v2": "1" } |
+----------------------------+

Upvotes: 1

Related Questions