雪花绑定变量

Question

如何使用雪花存储过程将current_date()bind变量插入到表中

创建或替换过程abc（“p_message_id”浮点数、“p_theater”字符串、“p_month”字符串、“p_message”字符串、“p_message”字符串、“p_start_date”字符串、“p_end_date”字符串、“p_action”字符串、“p_msg_type”字符串、“p_logged_user”字符串）返回variant LANGUAGE JAVASCRIPT为$$

Answer 1

您不能将函数添加为绑定变量，但为什么需要添加它们呢？您可以直接将它们放入SQL中：

create table abc(MESSAGE_ID number ,MESSAGE varchar,THEATER varchar ,CREATED_BY varchar,CREATE_DATE varchar,UPDATED_BY varchar, UPDATE_DATE varchar,MONTH
                 varchar,START_DATE varchar,END_DATE varchar,MESSAGE_TYPE varchar);
                                                 
                                                 
CREATE OR REPLACE PROCEDURE abc ( P_THEATER STRING, P_MONTH STRING, P_MESSAGE STRING, P_START_DATE STRING, P_END_DATE STRING, P_MSG_TYPE STRING, P_LOGGED_USER STRING )
RETURNS variant LANGUAGE JAVASCRIPT 
AS $$
var P_MESSAGE = P_MESSAGE;
var query= "INSERT INTO ABC (MESSAGE_ID,MESSAGE,THEATER,CREATED_BY,CREATE_DATE,UPDATED_BY, UPDATE_DATE,MONTH,START_DATE,END_DATE,MESSAGE_TYPE) VALUES (E2E_BANNER_MSG_S.NEXTVAL,:1,:2,:3,current_date,:3,current_date,:4,:5,:6, :7)"; 
var sql = snowflake.createStatement ({ sqlText: query, binds :[P_MESSAGE, P_THEATER, P_LOGGED_USER, P_MONTH, P_START_DATE, P_END_DATE, P_MSG_TYPE ]});
var result = sql.execute(); 
$$;
                                                 
CALL abc( 'Arena', 'Jan', 'Test message', '2021-01-01' , '2021-01-10', 'Open', 'Jack' );

select * from abc;