SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
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
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
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”

SAP HANA Procedure Example

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)”

SAP HANA Procedure Example

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.

Continue reading:
Check the next example on How to Use SIGNAL to Throw Exception




Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy