This app is an All-In-One package to provide everything to HANA Lovers.
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
In this article we will show an example on - How to use Exception inside procedures.
Exception handling is a method for handling exception and completion conditions in an SQLScript procedure. There are three tools can be used:
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 COLUMN TABLE <SCHEMA_NAME>.TABLE1 (
ID INTEGER PRIMARY KEY,
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) )
SQL SECURITY INVOKER
Write your procedure logic
DECLARE EXIT HANDLER FOR SQLEXCEPTION
ex_message := 'SQL Exception occured. Error Code is: ' || ::SQL_ERROR_CODE || ' Error message is: ' || ::SQL_ERROR_MESSAGE;
INSERT INTO "<SCHEMA_NAME>"."TABLE1" VALUES( :ip_id, :ip_name);
ex_message := 'Product "' || :ip_id || '" inserted successfully';
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)”
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.