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
How to Use RESIGNAL to Throw Exception


In the previous article How to Use SIGNAL to Throw Exception, we learnt how to implement SIGNAL in exception handling.
In this example we will learn – How to use RESIGNAL in exception.

Introduction:

The RESIGNAL statement raises an exception on the action statement in exception handler. If error code is not specified, RESIGNAL will throw the caught exception.

Difference between SIGNAL and RESIGNAL:
When using SIGNAL, you must point out the SQL ERROR CODE or CONDITION of the exception. But RESIGNAL can be used in the action part of an EXIT HANDLER all alone.
In this example we will use RESIGNAL to throw exception.

Create Table:

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

Note: If you have already created this table in the previous example, no need to create it again.  

Create procedure:

Copy and paste the below script to create the procedure.
--REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE "<SCHEMA_NAME>"."EXCEPTION_EXAMPLE_WITH_RESIGNAL" (
        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 empty_name CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE EXIT HANDLER FOR empty_name RESIGNAL;
IF :ip_name = '' THEN
    SIGNAL empty_name SET MESSAGE_TEXT = 'name cannot be empty';
END if;
INSERT into "<SCHEMA_NAME>"."TABLE1"
          values( :ip_id, :ip_name);
ex_message := 'Record inserted successfully';  
END;

Call procedure:

Call the procedure using below statement.
CALL <SCHEMA_NAME>."EXCEPTION_EXAMPLE_WITH_RESIGNAL"(10, '', ?);

This will RESIGNAL the exception to caller.

SAP HANA Procedure Example

Continue reading:
Check the next example on Cursor in HANA




Support us by sharing this article.



Explore More
Close X
Close X

2 thoughts on “SAP HANA Procedure Example – RESIGNAL and Exception Handling in HANA

  1. Tushar Upadhyay says:

    Hello Author,

    Very nice article 🙂
    I would also like to point out to an error ,

    CALL .”EXCEPTION_EXAMPLE_WITH_SIGNAL”(10, ”, ?);
    should be
    CALL .”EXCEPTION_EXAMPLE_WITH_RESIGNAL”(10, ”, ?);

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