IF FAIL

This batch SQL command displays a message or terminates the batch process in the following circumstances:

• an error occurs.
• a query retrieves no records.
• a DELETE, UPDATE or INSERT command affects no records.

The syntax is:

IF FAIL {"message" | EXIT };

where message is the message to be displayed upon failure. After displaying the message, the system waits for the user to press Enter or click OK before continuing with the batch file.

If EXIT is used, the system terminates the batch process when a failure occurs.

The IF FAIL command must be placed after the query statement to which it will apply. If other batch commands are associated with this statement (e.g., DISPLAY, TERSE), IF FAIL must be placed after them.

For example:

UPDATE partsupp
  SET qty=qty*1.25
  WHERE sno IN
(SELECT sno
     FROM supplier
     WHERE city = 'LONDON');
DISPLAY;
IF FAIL "No records updated. Aborting";
IF FAIL exit;

This example updates the partsupp table and displays an error message if the UPDATE command does not update any records. The second IF FAIL terminates the script after the user acknowledges the error message.