Snowflake Stored Procedure Not Inserting Record Though No Error

Question

Can someone tell me why this would run but not actually insert a record? When I return the actual query as a variable the code works perfectly in Snowflake:

CREATE OR REPLACE PROCEDURE Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.SP_FULL_AND_IS_HERRING_MOVE_CURRENT_DELETED_TO_PCFIVE(ACTION_TYPE VARCHAR,UPLOAD_ID VARCHAR,VENDOR VARCHAR)  
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$ 

var sql_textA = "INSERT INTO Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.RAW_CATALOGS (M_PDT_CODE) SELECT M_PDT_CODE FROM Z_CATALOGS_NEW.PUBLIC.CATALOGS_RAW WHERE M_VENDOR_NAME='HERRING' AND M_PDT_CODE NOT IN (SELECT M_PDT_CODE FROM Z_PC_FIVETRAN_DB.BLOB_RAW_CATALOGS.RAW_CATALOGS WHERE M_UPLOAD_ID='" +UPLOAD_ID+ "' AND M_PDT_CODE IS NOT NULL)"
var sql_commandA = snowflake.createStatement({ sqlText: sql_textA})

if (VENDOR=='HERRING' && ACTION_TYPE=='FULL')
{ 
var db = sql_commandA.execute()
return sql_textA
} 
else
{
return 'Not HERRING Or Not FULL'
}
$$;

Rajib Deb Rajib Deb · Accepted Answer · 2020-09-09T20:59:22

I ran your code and am able to insert rows. Only thing I can think of is if AUTOCOMMIT is turned off for you and if you need to explicitly call COMMIT. Try to call COMMIT after the INSERT and see if it works.

ALTER SESSION SET AUTOCOMMIT = FALSE;
CREATE OR REPLACE TABLE CATALOGS_RAW(M_VENDOR_NAME VARCHAR, M_PDT_CODE VARCHAR);
CREATE TABLE RAW_CATALOGS(M_PDT_CODE VARCHAR,M_UPLOAD_ID VARCHAR);

INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0002');
INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0003');
INSERT INTO CATALOGS_RAW VALUES('HERRING','PDT0005');
COMMIT;

SELECT * FROM CATALOGS_RAW;

INSERT INTO RAW_CATALOGS VALUES('PDT0001','UPD0001');
SELECT * FROM RAW_CATALOGS;



CREATE OR REPLACE PROCEDURE TESTPROC(ACTION_TYPE VARCHAR,UPLOAD_ID VARCHAR,VENDOR VARCHAR)  
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$ 
var sql_textA = "INSERT INTO RAW_CATALOGS (M_PDT_CODE) SELECT M_PDT_CODE FROM CATALOGS_RAW WHERE M_VENDOR_NAME='HERRING' AND M_PDT_CODE NOT IN (SELECT M_PDT_CODE FROM RAW_CATALOGS WHERE M_UPLOAD_ID='" +UPLOAD_ID+ "' AND M_PDT_CODE IS NOT NULL)"
var sql_commandA = snowflake.createStatement({ sqlText: sql_textA})

if (VENDOR=='HERRING' && ACTION_TYPE=='FULL')
{ 
var db = sql_commandA.execute()
return sql_textA
} 
else
{
return 'Not HERRING Or Not FULL'
}
$$; 

CALL TESTPROC('FULL','UPD0003','HERRING');
COMMIT;

Snowflake Stored Procedure Not Inserting Record Though No Error

1 Answers