Databases Reference
In-Depth Information
There are several situations where your batch might need to make a deci-
sion on what to do next based on current conditions or values. You do this with
IF . . . ELSE. The basic syntax is:
IF
boolean_expression
[BEGIN]
statement_block
[END]
[ELSE
[BEGIN]
statement_block
[END]]
This is one of the places where you used statement blocks to identify sets
of SQL statements, so you can identify which statements are associated with IF
and which are associated with ELSE. It's easiest to understand how this works
by looking at an example. Consider the following:
If there are no items currently on order, run ORDERPROC to generate restock-
ing orders. If there are items shown as on order, first run RECPROC to receive
any pending orders that have been posted and then run ORDERPROC().
There is a decision to be made based on whether or not there are any items
on order. This is the basis of your
Boolean expression,
which must evaluate as
True or False. Here's what the batch might look like:
IF (SELECT COUNT() FROM PRODUCT WHERE QOO = 0) = 0
BEGIN
ORDERPROC
END
ELSE
BEGIN
RECPROC
ORDERPROC
END
The statements are indented to make the batch easier to read. The indenta-
tions have no effect on execution. If the count of items with a QOO (quantity
on order column) value of 0 is equal to zero, then there is nothing on order and
you need to run ORDERPROC. If you get any value other than zero, execution
skips to the statements following the ELSE clause and you run RECPROC and
ORDERPROC. Technically, you are not required to use BEGIN and END when
executing a single statement, like the statement after the IF clause, but it's good
to get in the habit of using them in case you need to go back and make changes
later, such as adding more statements.
It's a good idea to document everything you do when writing scripts. It acts
as a reminder of what you did and why in case there are problems or a need to
modify the script later. You can embed nonexecuting statements in a batch or
