CODEWITHSUNDEEP
snowflake-cloud-data-platform
Ankit Srivastava
Ankit Srivastava

Reputation: 195

How do we perform Error Handling in SnowSQL in Snowflake

Do we know how to perform error handling in SnowSql.

Ex: A SnowSQL has 10 SQL Steps, capture if the previous step executed successfully. Let's say step 5 failed due to some issue then capture the same in output log. Or Step 5 failed but ignore that failure and move forward in the SnowSql

Upvotes: 1

Views: 1362

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

You can create a stored procedure and run each statement in a Try/Catch block.

The relevant section would look like this:

    stmt = snowflake.createStatement("select 1");
    var rs;
    try{
        rs = stmt.execute();
        return stmt.getQueryId() + ":" + "SUCCESS";
    }
    catch(err){
        return GetLastQueryID() + ":" + "ERROR:" + err.message;
    }

If the script should terminate on error, you can exit with the error. If the script should continue, you can report on the error and keep going.

I actually wrote a stored procedure to do exactly this. It reads SQL statements from a Snowflake table. There's a column that sets the order of the statements to run. Use the 1970's trick of numbering the steps in increments of 10 or 100 to allow new steps in between if needed. It also allows setting simple substitution variables.

https://github.com/GregPavlik/snowflake_script_runner

Upvotes: 1

Related Questions