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 SIGNAL to Throw Exception


In the previous article Exception Handling in HANA, we learnt how to implement exception handling in SAP HANA.
In this example we will learn – How to use SIGNAL in exception.

Introduction:

We can use the SIGNAL to throw exception.
SIGNAL:
The SIGNAL statement can be used to explicitly raise an exception from within your procedures.
The error code used must be within the user-defined range of 10000 to 19999.

In this example we will check if the input parameter for NAME is empty string or not. If its empty string, we will throw a user-defined 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_SIGNAL" (
        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
BEGIN
  ex_message := 'Error Code ' || ::SQL_ERROR_CODE || ' ' || ::SQL_ERROR_MESSAGE;  
END;
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_SIGNAL"(10, '', ?);

This will trigger the exception because the name is empty string.
SAP HANA Procedure Example

But if we call the procedure with a valid name, then it will work. For example:
CALL <SCHEMA_NAME>."EXCEPTION_EXAMPLE_WITH_SIGNAL"(10, 'ABC', ?);

SAP HANA Procedure Example

Continue reading:
Check the next example on How to Use RESIGNAL 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