Introduction to batches

ASA SQL User's Guide
Using Procedures, Triggers, and Batches

Introduction to batches

A simple batch consists of a set of SQL statements, separated by semicolons or separated by a separate line with just the word go on it. The use of go is recommended. For example, the following set of statements form a batch, which creates an Eastern Sales department and transfers all sales reps from Massachusetts to that department.

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

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

Interactive SQL and batches

Interactive SQL parses a list of semicolon-separated statements, such as the above, before sending them to the server. In this case, Interactive SQL sends each statement to the server individually, not as a batch. Unless you have such parsing code in your application, the statements would be sent and treated as a batch. Putting a BEGIN and END around a set of statements causes Interactive SQL 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:

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

If you run this batch twice from Interactive SQL, it creates the table the first time you run it and displays the message in the Interactive SQL Messages pane the next time you run it.