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.ExecuteIn 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.
See Also
Other Resources
GO (Transact-SQL)
sqlcmd Utility
osql Utility