An exception block is a Snowflake Scripting construct that handles errors in stored procedures. Normal execution skips it; execution resumes there when an error occurs. section 4.3.3
Use an exception block to:
- Reverse a transaction or perform other remediation actions after failure. section 4.3.3
- Return a diagnostic message containing SQLERRM, the error message stored by Snowflake. section 4.3.3
- Route different failures through WHEN clauses for built-in or custom exceptions; WHEN OTHER handles remaining exceptions. section 4.3.3
Listing 4.9 Exception handling in a stored procedure
use database BAKERY_DB;
use schema TRANSFORM;
create or replace procedure LOAD_CUSTOMER_ORDERS()
returns varchar
language sql
as
$$
begin
merge into CUSTOMER_ORDERS_COMBINED tgt... #1
return 'Load completed. ' || SQLROWCOUNT || ' rows affected.';
exception #2
when other then
return 'Load failed with error message: ' || SQLERRM;
end;
$$
;
A stored procedure wraps a merge and success return with an EXCEPTION block that catches all other failures and returns SQLERRM. section 4.3.3
