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.

But if we call the procedure with a valid name, then it will work. For example:

Click here to go to Next Chapter. 4.11 RESIGNAL in Exception Handling