Specifying Batches

Batches are implemented as part of the database APIs.

• In ADO, a batch is the string of Transact-SQL statements enclosed in the CommandText property of a Command object:

  Dim Cmd As New ADODB.Command

  Set Cmd.ActiveConnection = Cn

  Cmd.CommandText = "SELECT * FROM Suppliers; SELECT * FROM Products"

  Cmd.CommandType = adCmdText

  Cmd.Execute

    

• In OLE DB, a batch is the string of Transact-SQL statements enclosed in the string used to set the command text:

  WCHAR* wszSQLString =

  L"SELECT * FROM Employees; SELECT * FROM Products";

  hr = pICommandText->SetCommandText

  (DBGUID_DBSQL, wszSQLString)

    

• In ODBC, a batch is the string of Transact-SQL statements enclosed on a SQLPrepare or SQLExecDirect call:

  SQLExecDirect(hstmt1,

  "SELECT * FROM Employees; SELECT * FROM Products",

  SQL_NTS):

    

• In DB-Library, a batch is comprised of the Transact-SQL statements stored in the command buffer using dbcmd or dbfcmd before dbsqlsend or dbsqlexec are called:

  dbcmd (dbproc,

  "SELECT * FROM Suppliers; SELECT * FROM Products");

  dbsqlexec (dbproc);

Some data access tools, such as Microsoft® Access, do not have an explicit batch terminator.

The GO Command

SQL Server Query Analyzer, the osql utility, and the isql utility use the GO command to signal the end of a batch. GO is not a Transact-SQL statement; it simply signals to the utilities how many SQL statements should be included in a batch. In SQL Server Query Analyzer and osql, all the Transact-SQL statements from one GO command to the next are put in the string sent to SQLExecDirect. In isql, all the Transact-SQL statements between GO commands are placed into the command buffer before being executed.

For example, if these statements are executed in SQL Server Query Analyzer:

SELECT @@VERSION

SET NOCOUNT ON

GO

SQL Server Query Analyzer does the equivalent of:

SQLExecDirect(hstmt,

"SELECT @@VERSION SET NOCOUNT ON",

SQL_NTS);

Because a batch is compiled into a single execution plan, a batch must be logically complete. The execution plan created for one batch has no ability to reference any variables declared in another batch. Comments must both start and end in one batch.

See Also