Examples of Using Batches

When submitting a batch, you can use the same format as the isql utility, which has a clear end-of-batch signal—the word “go” on a line by itself.

Here is a batch that contains two select statements in a single batch:

select count(*) from titles 
select count(*) from authors 
go

------------- 
          18 
 
(1 row affected) 
------------- 
          23 
 
(1 row affected)

You can create a table and reference it in the same batch. This batch creates a table, inserts a row into it, and then selects everything from it:

create table test 
  (column1 char(10), column2 int) 
insert test 
  values ("hello", 598) 
select * from test 
go

(1 row affected) 
column1  column2 
-------  -------
hello        598 
 
(1 row affected)

You can combine a use statement with other statements, as long as the objects you reference in subsequent statements are in the database in which you started. This batch selects from a table in the master database and then opens the pubs2 database. The batch begins by making the master database current; afterwards, pubs2 is the current database.

use master
go
select count(*) from sysdatabases 
use pubs2 
go

------------- 
           6 
 
(1 row affected)

You can combine a drop statement with other statements as long as you do not reference or re-create the dropped object in the same batch. This example combines a drop statement with a select statement:

drop table test 
select count(*) from titles 
go

------------ 
         18 
 
(1 row affected)

If there is a syntax error anywhere in the batch, none of the statements are executed. For example, here is a batch with a typing error in the last statement, and the results:

select count(*) from titles 
select count(*) from authors 
slect count(*) from publishers 
go

Msg 156, Level 15, State 1: 
Line 3:
Incorrect syntax near the keyword ’count’.

Batches that violate a batch rule also generate error messages. Here are some examples of illegal batches:

create table test 
    (column1 char(10), column2 int) 
insert test 
    values ("hello", 598) 
select * from test 
create procedure testproc as 
    select column1 from test 
go

Msg 111, Level 15, State 7: 
Line 6: 
CREATE PROCEDURE must be the first command in a 
query batch.

create default phonedflt as "UNKNOWN"
sp_bindefault phonedflt, "authors.phone"
go

Msg 102, Level 15, State 1:
Procedure ’phonedflt’, Line 2:
Incorrect syntax near ’sp_bindefault’.

The next batch works if you are already in the database you specify in the use statement. If you try it from another database such as master, however, you see an error message.

use pubs2 
select * from titles 
go

Msg 208, Level 16, State 1: 
Server ’hq’, Line 2: 
titles not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output)

drop table test 
create table test 
(column1 char(10), column2 int)
go

Msg 2714, Level 16, State 1: 
Server ’hq’, Line 2: 
There is already an object named ’test’ in the 
database.