How to Handle Error in Snowflake Procedures and Functions?

Snowflake Cloud data warehouse supports stored procedures and user defined functions to help migration from other relational databases such as Oracle, Teradata. You can write stored procedures using JavaScript APIs. You can also write user defined functions using JavaScript APIs. JavaScript support error handling using try/catch block. However, Snowflake also supports built-in functions such as try_cast to handle error during type conversion. In this article, we will check how to handle error in Snowflake procedures and functions.

How to Handle Error in Snowflake?

The Snowflake cloud database allows you to handle errors with the help of try/catch block when you write a procedure or function using the JavaScript API.

Handle Errors in Snowflake Stored Procedure

With the help of try/catch block, you can handle errors in a Snowflake stored procedure. You can encapsulate your SQL statements inside a JavaScript try block. If an error occurs, then JavaScript catch block can roll back all of the statements. The Snowflake stored procedure can also throw a pre-defined exception or a custom exception.

For example, consider following stored procedure try-catch block to capture error when non-existing function is called.

create procedure Snf_error_handling()
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  try {
      snowflake.execute( {sqlText: "SELECT func_dummy();"} );
      result = "Succeeded";
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt; 
      }
  return result;
  $$ ;

Handle Errors in Snowflake User Defined Functions (UDF)

Similar to Stored procedures, you can handle errors in Snowflake user-defined functions using JavaScript Try/Catch block. This method will apply only when you are using JavaScript UDFs. This method won’t work when you write SQL UDFs.

For example, the following example shows a JavaScript UDF that throws a custom exception:

CREATE FUNCTION func_snf_error_handling(P_IN float)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    try {
        if (P_IN < 0) {
            throw "Value cannot be negative!";
        } else {
            return "Valid Value.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

Following is the output.

SELECT func_snf_error_handling(-10.2);
+----------------------------------+
| FUNC_SNF_ERROR_HANDLING(-10.2)   |
|----------------------------------|
| Error: Value cannot be negative! |
+----------------------------------+

SELECT func_snf_error_handling(10.2);
+-------------------------------+
| FUNC_SNF_ERROR_HANDLING(10.2) |
|-------------------------------|
| Valid Value.                  |
+-------------------------------+

TRY_CAST Function to Handle Errors in Snowflake

The TRY_CAST is the special version of type conversion function CAST. It converts a value of one data type into another data type, but returns a NULL value instead of raising an error when the conversion cannot be performed.

For example,

>SELECT TRY_CAST ('123' AS INT ) AS ID;
+-----+
|  ID |
|-----|
| 123 |
+-----+

>SELECT TRY_CAST ('123q' AS INT ) AS ID;
+----+
| ID |
|----|
| NULL |
+----+

The TRY_CAST function is one of the useful built-in conversion functions that helps safe data type conversion.