雪花云数据平台从字符串数组中获取列名并使用它来修改SQL表

Question

我正在编写一个接受字符串的SQL存储过程。字符串包含一个列名列表，其中包含列名和类型

ex) "姓名;年龄">

ALTER TABLE T1添加列名VARCHAR

我在和雪花一起工作

我的逻辑是正确的，但是我正在努力正确地编写语法在下面的代码中，带数组的alter table语句是我卡住的地方如有任何帮助，不胜感激

CREATE OR REPLACE PROCEDURE ADD_COLUMNS_CONTEXTUAL_ATTRIBUTES("P_COL_NAME_SPACE_TYPE" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'Turns column string list into array'
EXECUTE AS CALLER
AS
$$
var arr = [];
var v_storing_passed_parameter = P_COL_NAME_SPACE_TYPE;
arr = v_storing_passed_parameter.split(',');

for (i = 0; i < arr.length; i++){
try{
var v_sqlCode = ***`ALTER TABLE CONTEXTUAL_ATTRIBUTES 
ADD COLUMN ' + arr[i];***
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;  
throw 'Encountered error in executing v_sqlCode. n' + errMessage;
}

};

return "success";
$$;
CALL ADD_COLUMNS_CONTEXTUAL_ATTRIBUTES('AGE NUMBER, NAME VARCHAR');

Answer 1

给你:

CREATE OR REPLACE PROCEDURE ADD_COLUMNS_CONTEXTUAL_ATTRIBUTES("P_COL_NAME_SPACE_TYPE" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'Turns column string list into array'
EXECUTE AS CALLER
AS
$$
var arr = [];
var v_storing_passed_parameter = P_COL_NAME_SPACE_TYPE;
arr = v_storing_passed_parameter.split(',');

for (i = 0; i < arr.length; i++){
try{
var v_sqlCode = `ALTER TABLE CONTEXTUAL_ATTRIBUTES ADD COLUMN ${arr[i]}`;
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;  
throw 'Encountered error in executing v_sqlCode. n' + errMessage;
}

};

return "success";
$$;