User's Guide
   PART 1. Working with Databases
     CHAPTER 10. 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

• Interactive SQL 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. Therefore a batch can contain only a single CREATE PROCEDURE statement.

  Using SELECT statements in batches

You can include one or more SELECT statements in a batch. 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 server uses the first SELECT statement in the batch to describe the result set.

A RESUME statement 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