snowflake Bind variable

Question

How can i insert current_date() bind variable into a table using snowflake stored procedure

CREATE OR REPLACE PROCEDURE abc ( "P_MESSAGE_ID" FLOAT, "P_THEATER" STRING, "P_MONTH" STRING, "P_MESSAGE" STRING, "P_START_DATE" STRING, "P_END_DATE" STRING, "P_ACTION" STRING, "P_MSG_TYPE" STRING, "P_LOGGED_USER" STRING ) RETURNS variant LANGUAGE JAVASCRIPT AS $$

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,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11);" var sql = snowflake.createStatement ({ sqltext: query, binds :[P_MESSAGE_ID, P_MESSAGE, P_THEATER, P_LOGGED_USER, current_date(), P_LOGGED_USER, current_date(), P_MONTH, P_START_DATE, P_END_DATE, P_MSG_TYPE]});

var result = sql.execute(); $$;

Iam getting the below error :

execution error: Uncaught ReferenceError: CURRENT_DATE is not defined in ABC at ' P_LOGGED_USER,' position 40

Can you help me to resolve this issue

Thanks, nikhil

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-07-28T11:12:56

You can not add functions as bind variables but why do you need to add them? You can directly put them into your 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;

snowflake Bind variable

1 Answers