Hi I have below table which I want to update using a procedure.
create or replace TABLE T_CALLBACK(
"TBL_CallBack_Parmeter" VARCHAR(200),
"TBL_CallBack_ID" VARCHAR(100),
"TBL_CallBack_Selektion" VARCHAR(50),
"TBL_CallBack_SubSelektion" VARCHAR(50),
"TBL_CallBack_Text" VARCHAR(16384),
"TBL_CallBack_Date" DATE,
"TBL_CallBack_Inaktiv" VARCHAR(3),
"TBL_CallBack_Username" VARCHAR(20),
"TBL_CallBack_Timestamp" TIMESTAMP_NTZ(9));
I have created this Procedure to fulfil the requirement :
CREATE OR REPLACE PROCEDURE "CALLBACK_UPDATE1"
("CALLBACK_PARAMETER" VARCHAR(200),
"CALLBACK_IDS" VARCHAR(100),
"SELEKTION" VARCHAR(50),
"SUB_SELEKTION" VARCHAR(50),
"TEXT_INPUT" VARCHAR(16384),
"DATE_INPUT" DATE,
"INAKTIV" VARCHAR(3),
"GID" VARCHAR(20))
RETURNS String not null
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
var command = `INSERT INTO "T_CALLBACK"(
"TBL_CallBack_Parmeter" = :1,
"TBL_CallBack_ID" = :2,
"TBL_CallBack_Selektion" = :3,
"TBL_CallBack_SubSelektion" = :4,
"TBL_CallBack_Text" = :5,
"TBL_CallBack_Date" = :6,
"TBL_CallBack_Inaktiv" = :7,
"TBL_CallBack_Username" = :8,
"TBL_CallBack_Timestamp" = current_timestamp()`;
try {
var cmd_dict = {sqlText: command, binds: [CALLBACK_PARAMETER,CALLBACK_IDS,SELEKTION, SUB_SELEKTION, TEXT_INPUT, DATE_INPUT.toISOString(), INAKTIV, GID]};
var stmt = snowflake.createStatement(cmd_dict);
stmt.execute();
return 'success';
} catch (err) {
result = "Procedure_Failed: insert Code: " + err.code + "\\n Message: " + err.message + "\\n Stack Trace:" + err.stackTraceTxt;
return result;
}
$$;
I am calling using :
call "CALLBACK_UPDATE1"
('Param1','P123CCN2','Active','Test','testing procedure','2022-01-01','T','ZOO4');
getting below error :
Procedure_Failed: insert Code: undefined\n Message: date_value is not defined\n Stack Trace:undefined
Can anyone suggest what I am doing wrong?
Main requirement is to insert into table if ID is not there else update the record of ID.