Exit command in SQL script

[arpitasethi]

Hello Experts,

Is there an 'Exit' like command in SQL script (for script based calculation view)?

Thanks and Regards,

Arpita

[Ravi_Channe · Accepted solution]

Hi Arpita,

I don't think there is any EXIT as such, but if you are using LOOPS (WHILE or IF) with a condition, then you can always make the condition false for the loop to complete.

For ex: if you have while loop with

   WHILE v_val < 50 DO

   END WHILE;

In the loop you can always set the v_val to 50 when you want to exit the loop.

Is this what you are looking for or is there something else.

Regards,

Ravi

[arpitasethi · Accepted solution]

Hi Ravi,

I wanted something like -

If x is null then

exit;

end if;

Ofcourse, I can put my code in the other condition for if, if there is no exit.

I just wanted to keep it clean.

Anyway, thanks for the input!

Regards,

Arpita

[Former Member · Accepted solution]

Just to add there are BREAK & CONTINUE statements that you could use to exit from loops - page 43 in http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

Thanks,

Anooj

[Ravi_Channe · Accepted solution]

Hi Anooj,

Welcome back.. . Missed your expert comments last few days.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi Hope you doing well!

[RichHeilman]

I've just tested this in a Rev61 system, and the RETURN statement works just fine for this.

CREATE PROCEDURE test_return ( in im_flag nvarchar(1))

          LANGUAGE SQLSCRIPT

          SQL SECURITY INVOKER

          --DEFAULT SCHEMA <default_schema_name>

          READS SQL DATA AS

BEGIN

/*****************************

          Write your procedure logic

*****************************/

  select 'This' from dummy;

select 'That' from dummy;

if :im_flag = 'Y' THEN

  RETURN;

end if;

select 'The Other' from dummy;

END;

Cheers,

Rich Heilman

[arpitasethi]

Thanks alot Ravi and Jody!!

[Former Member]

Hi folks,

Just a follow up that I found a bit more documentation on exception handling underneath the trigger section of the HANA SQL guide for SP5 (not the SQLScript guide as one might expect). Check out page 99 of the guide.

Another useful feature of this functionality is RESIGNAL. Example use case:

1) You want to stop execution of a stored procedure under certain circumstances but don't need to throw an error (i.e. you need something like 'return' in Java).

2) In order to implement this, you signal a custom condition that you defined call MYCOND, and you also defined an EXIT_HANDLER that handles exceptions.

3) You want to make sure that other SQL exceptions still get thrown (i.e. you don't want them 'caught up' in your generic exit handler.

In this case, you can do something like this:

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

IF (::SQL_ERROR_CODE != 10001) THEN

     RESIGNAL;

ELSE

     -- do whatever else you might want to do once your custom exception is caught. Kind of like 'finally' following a try/catch block

END;

-- in your proc, when you want to stop execution because, for example, you have your answer, signal your exception:

SIGNAL MYCOND SET MESSAGE_TEXT = 'If this exception is handled, this message won't make it to the client';

[Former Member]

After messing around with this a bit, there's a much cleaner approach:

DECLARE EXIT HANDLER FOR MYCOND

-- enter whatever code you want executed if any

All normal SQL exceptions that might be thrown will still be thrown without getting caught in your handler. No need for cludgy IF/ELSE.

[Former Member]

Hi Arpita,

One further comment. I'm not exactly sure what EXIT does, but my suspicion is that it's similar to 'return' in Java and other languages.

One common application programming requirement is data validation, with a return if required conditions aren't met - i.e. checking input data.

One solution in SQLScript is as follows:

1) At the beginning of your code (after BEGIN), declare a error condition:

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001; -- example

2) In your code, check required condition. if not met, signal your condition:

IF (requested_start >= requested_end) THEN

          SIGNAL MYCOND SET MESSAGE_TEXT = 'Requested start date must be before requested end date.';

END IF;

At this point an exception will be thrown with the error msg you specify.

How do you handle such exceptions gracefully?  After your condition declaration, declare an exit handler. The code following the exit handler declaration is run each time an exception is encountered, giving you an opportunity to handle exceptions gracefully.

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SELECT * FROM DEBUG_MSG;

So, the entire snippet in my case (requested_start and requested_end are input parameters):

-- use this to signal exceptions as required

DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

-- following catches exceptions and runs the code immediately following

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SELECT * FROM DEBUG_MSG;

-- check input dates.

-- check that dates are further from each other than requested duration.

IF (requested_start >= requested_end) THEN

          SIGNAL MYCOND SET MESSAGE_TEXT = 'Requested start date must be before requested end date.';

END IF;

[Former Member]

This concept, of course, could also be used inside of a loop, etc.

[arpitasethi]

Hi Jody,

Is it possible to have an exit handler for a condition like this -

:var = 1;

This way, when ever I have to call exit handler, I can set the variable.

Thanks alot!

Arpita

[Former Member]

Hi Arpita,

If you run the following procedure, you'll get an exception as expected.

If you uncomment the two commented lines, you'll see that the exception is handled and that a value is stored in a scalar output variable.

Let me know if this helps.

Cheers,

Jody

DROP PROCEDURE TESTP;
CREATE PROCEDURE TESTP(OUT a INTEGER) AS

BEGIN
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;

-- DECLARE EXIT HANDLER FOR SQLEXCEPTION
-- SELECT 5 INTO a FROM DUMMY;

SIGNAL MYCOND SET MESSAGE_TEXT = 'Enter a message here';
END;

CALL TESTP(?);

[arpitasethi]

Hi Jody,

I guess I was not clear..

I want to raise an exception based on the value in a variable and not the other way round!

Thanks,

Arpita

[Ravi_Channe]

Hi Arpita,

I think Jody already mentioned that. You can check the value of the variable and can SIGNAL the exception.

IF (variable = value) THEN

          SIGNAL MYCOND SET MESSAGE_TEXT = 'Requested start date must be before requested end date.';

END IF;

Surprisingly, I could not find any information on SIGNAL or Declaring custom exceptions (except for SQL exception in sample codes) in the SQL script guide.

This is quite an interesting and much awaited feature in SQL script and I am keen to know more about it.

Regards.

Ravi

[arpitasethi]

Yes!

Stupid me! Took sometime to actually understand.. Thanks Ravi!

Is it possible to not set the message? I do not want to throw an error message, just exit.

[Former Member]

Hi Ravindra,

Yes unfortunately official documentation on this is lacking but looks like it's in the works. I had the fortune of learning the details from a colleague (credit to Adam Baryla).

Cheers,

Jody

[arpitasethi]

Hi Jody,

I am not able to test right now, but the signal function will also work if I donot want to set an error message?

Basically, I do not want any user action or notification, just simple return in the logic.

Thanks alot so far!!!

Arpita

[Ravi_Channe]

In that case BREAK will also work.

Regards,

Ravi

[Former Member]

BREAK will get you out of a loop, but subsequent code will execute. Signaling your condition will stop execution of your procedure. If you don't want error messages, etc - just make sure to include the exit handler syntax above, and just don't execute any SQL following that exit handler.

Try it out once you get access to test system and let me know if you have any questions.