Statements allowed in batches

Chapter 20. Using Procedures, Triggers, and Batches
Statements allowed in batches

The following statements are not allowed in batches:

CONNECT or DISCONNECT statement
ALTER PROCEDURE or ALTER FUNCTION statement
CREATE TRIGGER statement
ISQL commands such as INPUT or OUTPUT

Otherwise, any SQL statement is allowed, including data definition statements such as CREATE TABLE, ALTER TABLE, and so on.

The CREATE PROCEDURE statement is allowed, but must be the final statement of the batch.

Using SELECT statements in batches

You can include one or more SELECT statements in a batch. There are limitations on the use of more than one SELECT statements, just as in stored procedures: multiple SELECT statements are allowed only if they return the same result columns.

The following is a valid batch:

     IF EXISTS(    SELECT *
                     FROM systable
                     WHERE table_name='employee' )
     THEN
         SELECT    emp_lname AS LastName,
                     emp_fname AS FirstName
         FROM employee;
         SELECT lname, fname
         FROM customer;
         SELECT last_name, first_name
         FROM contact;
     END IF

The alias for the result set is required only in the first SELECT statement, as the database engine uses the first SELECT statement in the batch to describe the result set.

A RESUME is required following each query to retrieve the next result set.

The following is not a valid batch, as the two queries return different result sets:

     IF EXISTS( SELECT * FROM systable
             WHERE table_name='employee' )
     THEN
         SELECT    emp_lname AS LastName,
                     emp_fname AS FirstName
         FROM employee;
         SELECT id, lname, fname
         FROM customer;
     END IF