Question Re: Exit command in SQL script in Technology Q&A

Exit command in SQL script

arpitasethi — Mon, 18 Feb 2013 10:01:49 GMT

Hello Experts,

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

Thanks and Regards,

Arpita

Re: Exit command in SQL script

Ravi_Channe — Mon, 18 Feb 2013 10:23:03 GMT

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

Re: Exit command in SQL script

arpitasethi — Mon, 18 Feb 2013 10:28:25 GMT

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

Re: Exit command in SQL script

Former Member — Mon, 18 Feb 2013 11:35:17 GMT

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

Re: Exit command in SQL script

Ravi_Channe — Mon, 18 Feb 2013 11:50:20 GMT

Hi Anooj,

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

Regards,

Ravi

Re: Exit command in SQL script

Former Member — Mon, 18 Feb 2013 13:05:45 GMT

Thanks Ravi Hope you doing well!

Re: Exit command in SQL script

Former Member — Mon, 18 Feb 2013 19:54:46 GMT

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;

Re: Exit command in SQL script

Former Member — Mon, 18 Feb 2013 19:55:50 GMT

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

Re: Exit command in SQL script

arpitasethi — Wed, 20 Feb 2013 11:17:31 GMT

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

Re: Exit command in SQL script

Former Member — Wed, 20 Feb 2013 17:53:09 GMT

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(?);

Re: Exit command in SQL script

arpitasethi — Thu, 21 Feb 2013 04:13:15 GMT

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

Re: Exit command in SQL script

Ravi_Channe — Thu, 21 Feb 2013 13:02:24 GMT

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

Re: Exit command in SQL script

arpitasethi — Thu, 21 Feb 2013 14:04:56 GMT

Thanks alot Ravi and Jody!!

Re: Exit command in SQL script

arpitasethi — Thu, 21 Feb 2013 14:09:38 GMT

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.

Re: Exit command in SQL script

Former Member — Thu, 21 Feb 2013 18:36:05 GMT

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

Re: Exit command in SQL script

arpitasethi — Fri, 22 Feb 2013 06:35:17 GMT

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

Re: Exit command in SQL script

Ravi_Channe — Fri, 22 Feb 2013 08:40:09 GMT

In that case BREAK will also work.

Regards,

Ravi

Re: Exit command in SQL script

Former Member — Fri, 22 Feb 2013 17:35:44 GMT

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.

Re: Exit command in SQL script

Former Member — Thu, 04 Apr 2013 04:41:44 GMT

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';

Re: Exit command in SQL script

Former Member — Fri, 05 Apr 2013 05:50:24 GMT

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.