Multiple Statements in a Single Batch

A batch of commands is accepted by the server. That group of T-SQL commands is then put through the five-step process. If there are errors at any step, with any command in the batch, the process is aborted and the error returned.

You can place multiple commands in a single batch, which can be compiled and executed as a group. Consider the following batch of commands:

select title from titles where title like “%Computer%”
select au_id, au_fname from author where au_lname = “Ringer”

title
-------------------------------------------------------------------
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Straight Talk About Computers
The Psychology of Computer Cooking
You Can Combat Computer Stress!
(5 row(s) affected)
au_id         au_fname
-----------   --------------------
998-72-3567   Albert
899-46-2035   Anne
(2 row(s) affected)

Two SELECT statements were parsed, compiled, and executed. Each SELECT returns its own result set. The results coming back from a single batch include, in this case, two result sets, one for each SELECT.

Two Batches, Two Statements

To execute the two statements in different batches, you can use the word GO in the ISQL/w program. It must appear on its own line, but case does not matter:

select title from titles where title like “%Computer%”
go
select au_id, au_fname from authors where au_lname = “Ringer”

title
-----------------------------------------------------------------
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Straight Talk About Computers
The Psychology of Computer Cooking
You Can Combat Computer Stress!
(5 row(s) affected)
au_id         au_fname
-----------   --------------------
998-72-3567   Albert
899-46-2035   Anne
(2 row(s) affected)

Output appears to be the same. In this second case, though, the first statement was sent to the server, compiled, and executed. The client then waited for the results of the first SELECT to come back. When the results were completely retrieved, the second command was sent to be parsed, compiled, and executed.

---

Warning:  
GO is the word used by ISQL/w, isql command line, and most other client utilities that access SQL Server. It is not, however, a T-SQL keyword. You may not pass GO to the server in an application program to separate batches—you must execute separate SQLExecDirect statements (ODBC) or dbexec() (DB-Library).

---

What Happens When There Is an Error in the Batch?

The difference between placing multiple commands in a batch and executing multiple batches is most evident when there is an error. Suppose I introduce an error in the second step, the object reference step:

select title from titles where title like “%Computer%”
select au_id, au_fname from BADTABLENAME where au_lname =    “Ringer”

Msg 208, Level 16, State 1
Invalid object name ‘BADTABLENAME’.

There is no table named BADTABLENAME, so the server raises the error during the second parsing step. This is a critical error; compilation cannot occur. Although the first SELECT is syntactically correct, processing cannot continue.

Now, observe what happens if the commands are executed in their own batches. If there is an error in a batch, processing continues with the next batch, if there is one. I’ve included three statements here to illustrate that. (Note: The last GO is optional.)

select title from titles where title like “%Computer%”
go
select au_id, au_fname from BADTABLENAME where au_lname = “Ringer”
go
select qty from sales where title_id =
go

title
------------------------------------------------------------------
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Straight Talk About Computers
The Psychology of Computer Cooking
You Can Combat Computer Stress!
(5 row(s) affected)
Msg 208, Level 16, State 1
Invalid object name ‘BADTABLENAME’.
qty
------
25
(1 row(s) affected)

The first and third batches were correct and were allowed to run. The second batch referenced a bad object and returned an error.

Performance and Batching

If you batch inserts or updates, performance will improve. This is because the server has less work to do. If you compile and execute one set of ten INSERT statements, it will be faster than compiling and executing ten separate code fragments.

The danger here is that if one of the inserts fails before the compile stage, all of the inserts will fail.