A batch is one or multiple T-SQL statements executed by SQL Server as a single unit. Batches are separated by a GO statement, which marks the end of one batch and beginning of another.
Although this seems like a fairly simple concept you must keep certain rules in mind when grouping your commands in batches. I will summarize important batch rules that apply to SQL Server 2000 and 2005, and offer a few examples below.
|TABLE OF CONTENTS
Executing stored procedures
Batches with variables
Errors within batches
Combining statements in a batch
Changing table structure
Executing a string with SP_EXECUTESQL
Rule: If a stored procedure name appears as the first statement in the batch, EXECUTE command is not required to call the stored procedure.
Rule: If any other statement in the same batch precedes the stored procedure name, specifying EXECUTE command is required.
Hence the first command in this example will work in pubs database, but the second one will fail:
USE pubs GO -- stored procedure call is the first command in the batch -- so it doesn't require EXEC reptq1 /* stored procedure call is NOT the first command although no error is generated the procedure "reptq1" isn't executed */ SELECT TOP 3 * FROM authors reptq1
Rule: With SQL Server 2005, attempting to call a stored procedure without EXECUTE will generate an error (unless the procedure name is specified as the first statement within the batch).
SELECT * FROM categoreis SalesByCategory 'seafood'
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'seafood'.
Rule: Local variables, including table variables, can only be referenced within a batch in which they have been declared. If you attempt to reference a local variable after the GO command you'll get an error.
DECLARE @au_id VARCHAR(9) SELECT @au_id = au_id FROM authors WHERE au_lname = 'carson' GO -- end of the batch SELECT @au_id
Server: Msg 137, Level 15, State 2, Line 2 Must declare the variable '@au_id'.
You will get a similar error when attempting to reference a table variable outside of the batch in which it was declared.
DECLARE @table_var TABLE ( table_column1 INT, table_column2 VARCHAR(32) ) INSERT @table_var SELECT 1, 'value' GO SELECT * FROM @table_var
Msg 1087, Level 15, State 2, Line 2 Must declare the table variable "@table_var".
Rule: Compiling errors in any statement within the batch will prevent the entire batch from executing.
For example, the following batch will fail because there is no object called customers1 in the current database:
DECLARE @i INT SET @i = 2 SELECT * FROM customers1 SELECT * FROM customers GO
Msg 208, Level 16, State 1, Line 3 Invalid object name 'customers1'.
Rule: Some runtime errors, such as constraint violations, will only fail the current statement.
For example, the following batch only executes all statements except the INSERT into employees table; this statement fails due to primary key violation:
DECLARE @i INT SELECT @i = 3 SELECT @i SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy FROM employees WHERE employeeid = @i SET IDENTITY_INSERT employees ON INSERT employees ( EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath) SELECT 1, 'blah', 'blah', 'blah', '', '1/1/01', '1/1/01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL SELECT 123 GO
Msg 2627, Level 14, State 1, Line 12
Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'dbo.employees'.
The statement has been terminated.
Rule: Keep in mind that erroneous statements have no effect on other statements executed in the same batch. So if you have changed data with one statement and the second statement in the batch fails, the results of the first transaction are committed.
For instance, the following batch errors out during the execution of the second statement, yet the first statement succeeds and data is changed:
UPDATE employees SET lastname = 'smith' WHERE lastname = 'buchanan' SELECT * FROM NonExistent GO
Rule: Stored procedures, views and user-defined functions cannot contain more than one batch.
Rule: CREATE PROCEDURE, CREATE VIEW and CREATE FUNCTION statements cannot be combined with any other statement in a single batch. If you specify any commands before CREATE PROCEDURE / VIEW / FUNCTION you get an error:
'CREATE PROCEDURE' must be the first statement in a query batch.
Rule: If you attempt to include GO statement in the stored procedure, all commands after GO will not be saved as part of the stored procedure code.
For example, the following command will work:
CREATE PROC test AS SELECT 1 GO SELECT * FROM authors
However, when you examine the contents of "test" procedure, you will only find the following:
CREATE PROC test AS SELECT 1
Rule: Other statements that cannot be combined with any other statements in a single batch include:
- CREATE RULE
- CREATE TRIGGER
- CREATE DEFAULT
Rule: Although you can't switch the database context within a stored procedure, you are allowed to do so in a batch.
For example, the following is a valid batch.
use master EXEC sp_updatestats USE northwind11 EXEC sp_updatestats GO
Rule: You can create a table and reference it in the same batch. However, you cannot add columns to an existing table and reference new columns in the same batch.
For instance, the following command will fail with SQL Server 2000.
CREATE TABLE new_test ( test_name VARCHAR(50) ) INSERT new_test SELECT 'test1' ALTER TABLE new_test ADD test_id INT, test_fact INT SELECT test_id, test_fact FROM new_test
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'test_id'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'test_fact'.
Rule: This is no longer true with SQL Server 2005. You can add columns and reference them within the same batch. The same set of commands in SQL Server 2005 will result in the following output.
test_id test_fact ----------- ----------- NULL NULL
Rule: When you use sp_executesql procedure to execute a string, the entire string is considered as a self-contained batch. That means you can declare variables and change their values within the string.
sp_executesql N'USE northwind DECLARE @catid INT SET @catid=2 SELECT * FROM categories WHERE categoryid = @catid'
|2||Condiments||Sweet and savory sauces, relishes, spreads and seasonings|
Rule: However, you can't enclose more than one batch in sp_executesql string, so the following statement will fail:
sp_executesql N'USE northwind11 DECLARE @catid INT SET @catid=2 SELECT categoryid, categoryname, description FROM categories WHERE categoryid = @catid GO SELECT * FROM employees WHERE employeeid = @catid'
Rule: The batch within sp_executesql statement isn't allowed to change the variable declared in another batch, which is why the following statement will fail.
DECLARE @catid INT EXEC sp_executesql N' SET @catid=2 SELECT categoryid, categoryname, description FROM categories WHERE categoryid = @catid'
Rule:The sp_executesql routine does allow substitution of parameter values within the same batch.
For example, the following batch declares variables and parameter format for sp_executesql, then executes a string with multiple parameters.
DECLARE @IntVariable INT, @StrVariable VARCHAR(30), @SQLString NVARCHAR(400), @Parameter NVARCHAR(200) /* Build the SQL string one time. */ SET @SQLString = N'SELECT LastName, FirstName, Title, BirthDate, HireDate FROM employees WHERE employeeid = @EmployeeID OR LastName = @LastName'; /* Specify the parameter format */ SELECT @Parameter = N'@EmployeeID INT,@LastName VARCHAR(30)' /* specify variable values */ SET @IntVariable = 2 SET @strVariable = 'buchanan' /* Execute the string */ EXECUTE sp_executesql @SQLString, @Parameter, @EmployeeID = @IntVariable, @LastName = @strVariable /* change variable values */ SET @IntVariable = 9 SET @strVariable = 'king' /* Execute the string with new variable values */ EXECUTE sp_executesql @SQLString, @Parameter, @EmployeeID = @IntVariable, @LastName= @strVariable
|Fuller||Andrew||Vice President, Sales||2/19/52||8/14/92|
About the author: Baya Pavliashvili is a DBA manager with HealthStream Inc. -- a leader in online healthcare education. In this role, he oversees database operations supporting more than one million users. Pavliashvili's primary areas of expertise include performance tuning, replication and data warehousing. He can be reached at firstname.lastname@example.org.
More information from SearchSQLServer.com
- Checklist: Prepare SQL Server for peak workloads
- Stored procedure: Execute T-SQL code from a file
- Checklist: SQL Server performance-tuning checklist
This was first published in January 2006