Home > Teratrax Source Guard > Help > Working with Batches and Statements

Working with Batches and Statements

The Editor pane in the SQL Query window allows you to write SQL statements and batches and execute them against the database server you are connected to. You can execute a complete script or only selected SQL statements in the SQL Query window. To execute a complete script, create or open the script in the Editor pane then press F5. To execute only selected SQL statements, highlight the lines of code in the Editor pane then press F5.

SQL Statements vs. Batches

A SQL statement is a single SQL command (for example, SELECT * FROM table1 or SET NOCOUNT ON). A batch on the other hand, is a number of SQL statements, separated by the GO command and sent to the server for execution as a whole unit.

The GO Command

GO is not a Transact-SQL statement; it is a command recognized by SQL utilities and Teratrax Source Guard to signal the end of a batch of SQL statements. Teratrax Source Guard interprets GO as a signal that it should send the current batch of SQL statements to the server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the script if this is the first GO. If the last batch does not include a GO at the end, one will automatically be added by Teratrax Source Guard.

A SQL statement cannot occupy the same line as a GO command. GO has to be the only command in the line.

Batch Execution Sequence

The statements in the batch are compiled into a single unit called the "execution plan". The statements in the execution plan are then executed one at a time. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.

A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

1. Most run-time errors stop the current statement and the statements that follow it in the batch.
2. A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

The statements executed before the one that encountered the run-time error are not affected. The only exception is when the batch is in a transaction and the error causes the transaction to be rolled back. In this case, any uncommitted data modifications made before the run-time error are rolled back.

for example, if there are 10 statements in a batch and the fifth statement has a syntax error, none of the statements in the batch are executed. If the 10 statements have no syntax errors but the second statement fails while executing, the results of the first statement are not affected since they are already executed by the time control was at the second statement.

Rules for Batches

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be altered and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Session Configuration (SET Options)

These are default settings for SET options in Teratrax Source Guard: