Snowflake Error handling – Procedure and Functions

Snowflake Cloud data warehouse supports stored procedures to support migration from other relational databases. Snowflake allows you to write stored procedure using JavaScript APIs. It also supports error handling using try/catch block. However, it also supports built-in functions such as try_cast to handle error during type conversion. In this article, we will check methods for handling error in Snowflake procedures and functions.

Snowflake Error handling

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

Snowflake Stored Procedure Error Handling

You can implement error handling in Snowflake Stored Procedures using JavaScript Try/Catch block. You can execute your SQL statements inside a try block. If an error occurs, then your catch block can roll back all of the statements. The stored procedure can throw a pre-defined exception or a custom exception.

For example, consider following stored procedure that try to execute non existent function.

create procedure error_handling()
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  try {
      snowflake.execute( {sqlText: "SELECT non_exist();"} );
      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;
  $$
  ;

Related Articles,

• Snowflake Stored Procedures,Syntax, Limitations and Examples

When you try to execute above procedure, it will throw custom exception and catch block will capture the same.

Following is the output.

> CALL error_handling();
+------------------------------------------+
| ERROR_HANDLING                           |
|------------------------------------------|
| Failed: Code: 100183                     |
|   State: P0000                           |
|   Message: SQL compilation error:        |
| Unknown function NON_EXIST               |
| Stack Trace:                             |
| At Snowflake.execute, line 4 position 16 |
+------------------------------------------+

Handle Error in Snowflake User Defined Functions (UDF)

You can handle errors in Snowflake user defined function using JavaScript Try/Catch block. This method will apply only when you are using JavaScript UDFs.

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

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

Related Articles,

• Snowflake User Defined Functions, Syntax and Examples

Following is the output.

>SELECT func_error_handling(-10);
+----------------------------------+
| FUNC_ERROR_HANDLING(-10)         |
|----------------------------------|
| Error: Value cannot be negative! |
+----------------------------------+

TRY_CAST Function to Handle Errors

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 allows you to safely convert one data type to another type.

Related Articles,

• Snowflake NULL Handling Functions, Usage and Examples
• Oracle DML LOG ERROR Alternative in Snowflake
• Rows Affected by Last Snowflake SQL Query – Example

Hope this helps 🙂