Specifying Batches

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 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 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 Query Analyzer:

SELECT @@VERSION
SET NOCOUNT ON
GO

SQL 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. For more information, see SQL Query Analyzer.

See Also

GO

osql Utility