|
A simple Transact-SQL batch consists of a set of SQL statements with no delimiters followed by a separate line with just the word GO on it. The following example creates an Eastern Sales department and transfers all sales reps from Massachusetts to that department. It is an example of a Transact-SQL batch.
INSERT INTO Departments ( DepartmentID, DepartmentName ) VALUES ( 220, 'Eastern Sales' ) UPDATE Employees SET DepartmentID = 220 WHERE DepartmentID = 200 AND State = 'MA' COMMIT GO
The word GO is recognized by Interactive SQL and causes it to send the previous statements as a single batch to the server.
The following example, while similar in appearance, is handled quite differently by Interactive SQL. This example does not use the Transact-SQL dialect. Each statement is delimited by a semicolon. Interactive SQL sends each semicolon-delimited statement separately to the server. It is not treated as a batch.
INSERT INTO Departments ( DepartmentID, DepartmentName ) VALUES ( 220, 'Eastern Sales' ); UPDATE Employees SET DepartmentID = 220 WHERE DepartmentID = 200 AND State = 'MA'; COMMIT;
To have Interactive SQL treat it as a batch, it can be changed into a compound statement using BEGIN ... END. The following is a revised version of the previous example. The three statements in the compound statement are sent as a batch to the server.
BEGIN INSERT INTO Departments ( DepartmentID, DepartmentName ) VALUES ( 220, 'Eastern Sales' ); UPDATE Employees SET DepartmentID = 220 WHERE DepartmentID = 200 AND State = 'MA'; COMMIT; END
In this particular example, it makes no difference to the end result whether a batch or individual statements are executed by the server. There are situations, though, where it can make a difference. Consider the following example.
DECLARE @CurrentID INTEGER; SET @CurrentID = 207; SELECT Surname FROM Employees WHERE EmployeeID=@CurrentID;
If you execute this example using Interactive SQL, you will get a "variable not found" error. This happens because Interactive SQL sends three separate statements to the server. They are not executed as a batch. As you have already seen, the remedy is to use a compound statement to force Interactive SQL to send these statements as a batch to the server. The following example accomplishes this.
BEGIN
DECLARE @CurrentID INTEGER;
SET @CurrentID = 207;
SELECT Surname FROM Employees
WHERE EmployeeID=@CurrentID;
END
Putting a BEGIN and END around a set of statements forces Interactive SQL to treat them as a batch.
The IF statement is another example of a compound statement. Interactive SQL sends the following statements as a single batch to the server.
IF EXISTS( SELECT *
FROM SYSTAB
WHERE table_name='Employees' )
THEN
SELECT Surname AS LastName,
GivenName AS FirstName
FROM Employees;
SELECT Surname, GivenName
FROM Customers;
SELECT Surname, GivenName
FROM Contacts;
ELSE
MESSAGE 'The Employees table does not exist'
TO CLIENT;
END IF
This situation does not arise when using other techniques to prepare and execute SQL statements. For example, an application that uses ODBC can prepare and execute a series of semicolon-separated statements as a batch.
Care must be exercised when mixing Interactive SQL statements with SQL statements intended for the server. The following is an example of how mixing Interactive SQL statements and SQL statements can be an issue. In this example, since the Interactive SQL OUTPUT statement is embedded in the compound statement, it is sent along with all the other statements to the server as a batch, and results in a syntax error.
IF EXISTS( SELECT *
FROM SYSTAB
WHERE table_name='Employees' )
THEN
SELECT Surname AS LastName,
GivenName AS FirstName
FROM Employees;
SELECT Surname, GivenName
FROM Customers;
SELECT Surname, GivenName
FROM Contacts;
OUTPUT TO 'c:\\temp\\query.txt';
ELSE
MESSAGE 'The Employees table does not exist'
TO CLIENT;
END IF
The correct placement of the OUTPUT statement is shown below.
IF EXISTS( SELECT *
FROM SYSTAB
WHERE table_name='Employees' )
THEN
SELECT Surname AS LastName,
GivenName AS FirstName
FROM Employees;
SELECT Surname, GivenName
FROM Customers;
SELECT Surname, GivenName
FROM Contacts;
ELSE
MESSAGE 'The Employees table does not exist'
TO CLIENT;
END IF;
OUTPUT TO 'c:\\temp\\query.txt';
Many statements used in procedures and triggers can also be used in batches. You can use control statements (CASE, IF, LOOP, and so on), including compound statements (BEGIN and END), in batches. Compound statements can include declarations of variables, exceptions, temporary tables, or cursors inside the compound statement.