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

[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.