• Article

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 Purchasing.Vendor; SELECT * FROM Production.Product"
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 HumanResources.Employee; SELECT * FROM Production.Product";
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 HumanResources.Employee; SELECT * FROM Production.Product",
SQL_NTS):

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

The GO Command

Microsoft SQL Server Management Studio, the **sqlcmd **utility, and the osql 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 Management Studio and osql, all the Transact-SQL statements from one GO command to the next are put in the string sent to SQLExecDirect.

For example, if these statements are executed in SQL Server Management Studio:

SELECT @@VERSION
SET NOCOUNT ON
GO

SQL Server Management Studio 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.