如何在Snowflake中将存储过程作为参数传递 [英] How to pass a stored procedure as a parameter in snowflake

问题描述

我有一个存储过程，它以另一个存储过程的名称和参数调用它

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

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

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 + "
  State: " + err.state;
     errMessage += "
  Message: " + err.message + v_sqlCode;
     errMessage += "
Stack Trace:
" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. 
' + 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 + "
  State: " + err.state;
     errMessage += "
  Message: " + err.message + v_sqlCode;
     errMessage += "
Stack Trace:
" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. 
' + errMessage;
   }
  return "success!";
$$;

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

推荐答案

综合以上所有注释，以下是当我在此处测试我的开发人员帐户时正在运行的更新存储过程。

备注：

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

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

• 反号对于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 + "
    State: " + err.state;
     errMessage += "
    Message: " + err.message + v_sqlCode;
     errMessage += "
  Stack Trace:
  " + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. 
  ' + 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 + "
    State: " + err.state;
     errMessage += "
    Message: " + err.message + v_sqlCode;
     errMessage += "
  Stack Trace:
  " + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. 
  ' + errMessage;
  }
  return "success!"; 
  $$;
  
  CALL sp_stacko_1('sp_stacko_2','P');
  

我希望这能帮助.Rich

附注：如果此(或其他)答案对您有帮助，请花些时间接受对您有帮助的答案(&Q；) 通过单击答案旁边的复选标记将答案从灰显"；切换为"；填充的"；。

这篇关于如何在Snowflake中将存储过程作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！