CODEWITHSUNDEEP
sqlstored-proceduressnowflake-cloud-data-platformprocedure
Anonymus
Anonymus

Reputation: 11

Snowflake Procedure to insert into table not working

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.

Upvotes: 1

Views: 615

Answers (1)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1616

looks like the closing bracket in your command sql is missing. Also if you want to INSERT if the ID is not present or UPDATE then you can use MERGE statement.

https://docs.snowflake.com/en/sql-reference/sql/merge.html

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()`;

Upvotes: 1

Related Questions