User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

A simple batch consists of a set of SQL statements, separated by semicolons. For example, the following set of statements form a batch:

Create an Eastern Sales department, and transfer all sales reps from Massachusetts to that department.

     INSERT
     INTO department ( dept_id, dept_name )
     VALUES ( 220, 'Eastern Sales' ) ;
     UPDATE employee
     SET dept_id = 220
     WHERE dept_id = 200
     AND state = 'MA' ;
     COMMIT ;

You can include this set of statements in an application and execute them together.

ISQL and batches A list of semicolon-separated statements, such as the above, is parsed by ISQL prior to sending to the database engine. In this case, ISQL sends each statement individually to the engine, not as a batch. Unless you have such parsing code in your application, the statements would be sent and treated as a batch. Other batches described below are sent by ISQL as a batch, not as a set of individual statements. Putting a BEGIN and END around a set of statements causes ISQL to treat them as a batch.

Many statements used in procedures and triggers can also be used in batches. You can use control statements (CASE, IF, LOOP, and so on), including compound statements (BEGIN and END), in batches. Compound statements can include declarations of variables, exceptions, temporary tables, or cursors inside the compound statement.

The following batch creates a table only if a table of that name does not already exist:

     BEGIN
         IF NOT EXISTS (
             SELECT * FROM SYSTABLE
             WHERE table_name = 't1' ) THEN
             CREATE TABLE t1 (
                     firstcol INT PRIMARY KEY,
                     secondcol CHAR( 30 )
             ) ;
         ELSE
             MESSAGE 'Table t1 already exists' ;
         END IF
     END

If you run this batch twice from ISQL, it creates the table the first time you run it, and prints the message on the server or engine message window the next time you run it.