stackoverflow-64500277-snowflake-working-with-stored-procedure-on-error-handling

64500277-snowflake-working-with-stored-procedure-on-error-handling.sql

CREATE OR REPLACE SCHEMA abc;

USE SCHEMA ABC;


CREATE  SEQUENCE  IF NOT EXISTS EXECUTION_SEQUENCE
   WITH 
   START  WITH   =  1
   INCREMENT  BY  =  1
   COMMENT = 'DEMO SEQUENCE ' ;



CREATE OR REPLACE TABLE abc.ERROR_LOG(EXECUTION_ID number , STATUS varchar, MESSAGE VARCHAR, START_TS timestamp_ltz , END_TS timestamp_ltz);


CREATE TABLE ABC.TEST_TABLE1(ID NUMBER, MESSAGE VARCHAR);

INSERT INTO ABC.TEST_TABLE1(ID, MESSAGE)
SELECT SEQ4() + 1 , RANDSTR(50, RANDOM()) 
FROM TABLE(GENERATOR(ROWCOUNT=>50));

SELECT * FROM ABC.TEST_TABLE1 ORDER BY 1 ASC;


CREATE OR REPLACE PROCEDURE abc.PROC_GET_COUNT(table_name varchar)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

// SQL Queries
var get_execution_id_sql = "select t.nextval from table(getnextval(EXECUTION_SEQUENCE)) t";

var get_count_sql = `select count(*) from abc.`+ TABLE_NAME +`;`

var result_set1 = snowflake.createStatement({sqlText: get_execution_id_sql}).execute();

result_set1.next();

var seq_num= result_set1.getColumnValue(1);

var insert_meta_sql1= `INSERT into abc.ERROR_LOG (EXECUTION_ID, STATUS, START_TS,MESSAGE) values ( '` +seq_num+ `', 'In_Progress', CURRENT_TIMESTAMP(), '`+TABLE_NAME+`')`;

try {
    message = 'In insert Metadata with start details';
    snowflake.execute({sqlText: insert_meta_sql1});

    message = 'In Process of get count';
    get_count_out = snowflake.execute ({sqlText: get_count_sql});
    get_count_out.next();
    rec_count = get_count_out.getColumnValue(1);
    
    upd_meta_sql = `UPDATE abc.ERROR_LOG SET END_TS = current_timestamp(), STATUS = 'SUCCESS', MESSAGE = '` + TABLE_NAME + ` - Total count: ` + rec_count + `'  where EXECUTION_ID = ` + seq_num + `;`
    
    message = 'In update Metadata with end details';
    snowflake.execute ({sqlText: upd_meta_sql});
    
} catch (err)  {
    message = `Error Details\r\nCode: ` + err.code + `\r\nState:` + err.state +`\r\nMessage: ` + err.message + `\r\nStack Trace: ` + err.stackTraceTxt;

    // **** you need to replace the single quotes that are being returned in your error message
    message = message.replace(/'/g, `"`);
    upd_meta_sql = `UPDATE abc.ERROR_LOG 
                    SET END_TS = current_timestamp()
                    ,STATUS = 'FAILED'
                    , MESSAGE = '` + message +`' where EXECUTION_ID = ` + seq_num + `;`
    snowflake.execute ({sqlText: upd_meta_sql});
    return "Failed: " + message + ' -- ' +err; 
    }

return 'SUCCESS';
$$;



CALL abc.PROC_GET_COUNT('TEST_TABLE1');



CALL abc.PROC_GET_COUNT('TEST_TABLE_MISSING');


SELECT * FROM abc.ERROR_LOG ORDER BY 1 ASC;