Batches and Scripts

Batch

A batch is a text file or string containing a series of SQL statements.

A batch can also contain variables and control flow statements, like IF...ELSE and WHILE.

The statements are terminated by a semicolon ;.

For the moment, semicolons can be omitted but they may become mandatory in the future.

Semicolons should terminate all statements, except after the following:

• syntaxical grouping statements BEGIN...END
• control flow statements IF...ELSE and WHILE
• RSQL Client rcli special commands GO, EXIT or QUIT.

Example of a batch:

DECLARE @i INT = 100;

BEGIN TRANSACTION;

WHILE @i < 105
  BEGIN
  INSERT INTO t VALUES(@i, 'hello_' + CAST(@i AS VARCHAR));
  SET @i += 1;
  END

COMMIT TRANSACTION;

SELECT * FROM t;

A batch is sent as a whole to the server. The server compiles the batch and executes it.

If an error occurs during compilation, the batch is not executed and an error is returned to the client.

If an error occurs during execution, the batch is terminated immediately, the active transaction is rolled back and an error is returned to the client.

On MS SQL Server, the execution continues by default. To force MS SQL Server to terminate the batch immediately on error, the option XACT_ABORT must be set to ON.

Script

A script is a text file, processed by the RSQL Client rcli.

A single script contains one or many batches:

• In a script, batches are separated by the special commands GO, EXIT or QUIT.
• The RSQL Client will send each batch in turn to the server, waiting for the batch to finish before sending the next.
• Incompatible statements can thus be grouped in different batches.

This script contains three batches, separated by the GO command.

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

CREATE TABLE mytable (a INT IDENTITY NOT NULL, b VARCHAR(50) NULL);
GO

INSERT mytable VALUES ('Row #1'), ('Row #2');
SELECT * FROM mytable;
GO

DROP TABLE mytable;