MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 9: Executing Statements
A batch of SQL statements is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements. In some implementations, the entire batch statement is executed before any results are available. This is often more efficient than submitting statements separately, because network traffic can often be reduced and the data source can sometimes optimize execution of a batch of SQL statements. In other implementations, calling SQLMoreResults triggers the execution of the next statement in the batch. ODBC supports the following types of batches:
INSERT INTO Orders (OrderID, CustID, OpenDate, SalesPerson, Status)
VALUES (2002, 1001, {fn CURDATE()}, 'Garcia', 'OPEN');
INSERT INTO Lines (OrderID, Line, PartID, Quantity)
VALUES (2002, 1, 1234, 10);
INSERT INTO Lines (OrderID, Line, PartID, Quantity)
VALUES (2002, 2, 987, 8);
INSERT INTO Lines (OrderID, Line, PartID, Quantity)
VALUES (2002, 3, 566, 17);
INSERT INTO Lines (OrderID, Line, PartID, Quantity)
VALUES (2002, 4, 412, 500)
CREATE PROCEDURE GetCustInfo (@CustomerID INT) AS
SELECT * FROM Customers WHERE CustID = @CustomerID
SELECT OrderID FROM Orders
WHERE CustID = @CustomerID AND Status = 'OPEN'
The CREATE PROCEDURE statement itself is not a batch of SQL statements. However, the procedure it creates is a batch of SQL statements. No semicolons separate the two SELECT statements because the CREATE PROCEDURE statement is specific to SQL Server, and SQL Server does not require semicolons to separate multiple statements in a CREATE PROCEDURE statement.
INSERT INTO Lines (OrderID, Line, PartID, Quantity)
VALUES (?, ?, ?, ?)
If a data source does not support arrays of parameters, the driver can emulate them by executing the SQL statement once for each set of parameters. For more information, see "Statement Parameters" and "Arrays of Parameter Values," later in this chapter.
The different types of batches cannot be mixed in an interoperable manner. That is, how an application determines the result of executing an explicit batch that includes procedure calls, an explicit batch that uses arrays of parameters, and a procedure call that uses arrays of parameters is driver-specific.