snowflake-cloud-data-platform - 如何将存储过程作为雪花中的参数传递

Question

我有一个存储过程，它使用另一个存储过程的名称来调用它并使用参数

但是，当我尝试执行此存储过程时出现错误

我究竟做错了什么？从下面的代码中，我想我知道如何调用传递的存储过程，但是我想知道是不是我的语法不正确。

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".LOG_PROCEDURES("P_PROCEDURE_NAME" VARCHAR, "P_PROCEDURE_PARAMETERS" VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Inserts into the table PROCEDURE_LOGGING information about the last runned procedure'
EXECUTE AS CALLER 
AS
$$
  var v = P_PROCEDURE_PARAMETERS;
  var v_sqlCode;
  if(P_PROCEDURE_PARAMETERS.length > 0){
    v_sqlCode = `CALL ` + P_PROCEDURE_NAME  + `(` + P_PROCEDURE_PARAMETERS + `)`;
    
  }else{
     return "there was no parameter passed";
   }

  try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
  return "success!"; 
$$;

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".TESTFORLOG("P" VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Inserts into the table PROCEDURE_LOGGING information about the last runned procedure'
EXECUTE AS CALLER 
AS 
$$
    var v_sqlCode = 'CREATE TABLE ' + P + `(name varchar)`;
      try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
  return "success!";
$$;

CALL ADMINDB.TOOLKIT.LOG_PROCEDURES('TESTFORLOG','P');

标签： snowflake-cloud-data-platform

Answer 1

结合以上所有评论，这里是更新的存储过程，当我在我的开发帐户上测试时这些过程正在运行。

笔记：

• 如果进行跨模式/数据库对象引用，请注意上下文并尽可能使用完全限定名称

• 调试时查看您的查询历史记录

• 反引号对于 SQL 来说很棒，我在下面有一个带有和一个没有的示例

• 我喜欢将 SQL 放在一个块中（带有反引号），然后进行替换，如图所示。

  CREATE OR REPLACE PROCEDURE sp_stacko_1(p1 varchar, p2 varchar)
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS caller
  AS 
  $$
  
  var p_procedure_name = P1;
  var p_params = P2;
  
  if(p_params.length > 0) {
    v_sqlCode = `
      CALL <p_procedure_name>('<p_params>')
      `;
    v_sqlCode = v_sqlCode.replace("<p_procedure_name>", p_procedure_name);
    v_sqlCode = v_sqlCode.replace("<p_params>", p_params);
  }
  else {
    return "there was no parameter passed";
  }
  
  try {
    snowflake.execute({sqlText: v_sqlCode});
  }
  catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
  }
  return "success!"; 
  $$;
  
  CREATE OR REPLACE PROCEDURE sp_stacko_2(p3 varchar)
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS caller
  AS 
  $$
  
  var p_table_name = P3;
  var v_sqlCode = "CREATE TABLE <table_name> (name varchar)";
  v_sqlCode = v_sqlCode.replace("<table_name>", p_table_name);
  
  try {
    snowflake.execute({sqlText: v_sqlCode});
  }
  catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
  }
  return "success!"; 
  $$;
  
  CALL sp_stacko_1('sp_stacko_2','P');
  

我希望这会有所帮助...丰富

ps 如果这个（或另一个）答案对您有帮助，请花点时间“接受”有帮助的答案，方法是单击答案旁边的复选标记，将其从“灰色”切换为“已填写”。