The examples in this section illustrate batches using the format of 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.
