Error handling in Stored Procedure

Read Time:1 Minute, 35 Second

As you know Snowflake chose to use Javascript as the compositional language for expressing your logic. Troubleshooting JS has been challenging for us so Snowflake has come up with a better mechanism for enabling logging. However, The error handling in Stored procedure (using Javascript) can be implemented using JavaScript Try/Catch.

Similarly, Users in all clouds and all regions can now use Snowflake’s new stored procedure language to:

Implement and invoke complex business logic, such as ETL procedures, in an easier and more modern language within Snowflake to support arrays, exceptions, control structures, and garbage collection—to name a few relevant language attributes
Deploy well-known access control primitives without the need to manage an external environment
Build and run dynamic SQL
In conclusion, Do all of the above with seamless support for both structured and semi-structured data

Stored Procedures Introduction:

With the introduction of Snowflake’s stored procedures, users will be able to:

Use a first-class SQL object (procedure) along with the corresponding DDL statements
Grant schema privileges on stored procedures
Make use of procedural logic via IF/ELSE statements
Moreover, Iterate over result sets
Finally Implement error handling via TRY/CATCH
Use the caller’s rights stored procedure to run all the supported SQL as the current user

Firstly, Error logging table to be created which will hold the Erroneous data:

CREATE OR REPLACE TABLE error_log (error_code number, error_state string, error_message string, stack_trace string);

Finally, JavaScript procedure implementing Error handling using TRY/CATCH exception clause:

CREATE OR REPLACE PROCEDURE broken()
RETURNS varchar
NOT NULL
LANGUAGE javascript
AS $$
var result;
try {
snowflake.execute({ sqlText: “Invalid Command!;” });
result = “Succeeded”;
} catch (err) {
result = “Failed”;
snowflake.execute({
sqlText: `insert into error_log VALUES (?,?,?,?)`
,binds: [err.code, err.state, err.message, err.stackTraceTxt]
});
}
return result;
$$;