Exception Handling in HANA

In this article we will show an example on - How to use Exception inside procedures.

Introduction:

Exception handling is a method for handling exception and completion conditions in an SQLScript procedure. There are three tools can be used:

• EXIT HANDLER
• CONDITION
• SIGNAL or RESIGANL.

The DECLARE EXIT HANDLER parameter allows you to define exception handlers to process exception conditions in your procedures.
You can explicitly signal an exception and completion condition within your code using SIGNAL and RESIGNAL.

In this example we will try to insert record into a table in procedure. If the record is already there, then it will throw a “Unique constraints violated” error.

Create Table:

CREATE COLUMN TABLE <SCHEMA_NAME>.TABLE1 (
        ID INTEGER PRIMARY KEY,
        NAME VARCHAR(10)
);

Create procedure:
Copy and paste the below script to create the procedure.
--REPLACE < SCHEMA_NAME > WITH YOUR SCHEMA NAME
CREATE PROCEDURE "<SCHEMA_NAME>"."EXCEPTION_EXAMPLE1" (
        IN ip_id integer,
        IN ip_name nvarchar(40),
        OUT ex_message nvarchar(200) )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
BEGIN
/*****************************
Write your procedure logic
 *****************************/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ex_message := 'SQL Exception occured. Error Code is: ' || ::SQL_ERROR_CODE || ' Error message is: ' || ::SQL_ERROR_MESSAGE;
END;
INSERT INTO "<SCHEMA_NAME>"."TABLE1" VALUES( :ip_id, :ip_name);
ex_message := 'Product "' || :ip_id || '" inserted successfully';
END;

Call procedure:

Call the procedure using below statement.
CALL <SCHEMA_NAME>."EXCEPTION_EXAMPLE1"(1, 'A', ?);

First time we call this procedure, we will get below message as output.
“Product "1" inserted successfully”

Next time if we call the procedure with same input parameter, we will get below message as error.

“SQL Exception occured. Error Code is: 301 Error message is: unique constraint violated: Table(TABLE1)”

Explanation:

When exception occurs, the invocation well be suspended, and the subsequent operations will not be executed. After suspending the procedure, the action operations of EXIT HANDLER will be executed.

Notice: We can use "::SQL_ERROR_CODE","::SQL_ERROR_MESSAGE" to get the SQL ERROR CODE and related error message of the caught exception.

Click here to go to Next Chapter. 4.10 SIGNAL in Exception Handling