SQL Server batch rules

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. This command must appear on a separate line from all other commands.

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

Executing stored procedures

Return to Table of Contents

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'

Results:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'seafood'.

Batches with variables

Return to Table of Contents

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

Result:

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

Results:

Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@table_var".

Rule: Local temporary tables can be used by any batch within a single connection.

Errors within batches

Return to Table of Contents

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

Result:

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

Results:
-----------
3

EmployeeID	LastName	FirstName	Title	TitleOfCourtesy
3	Leverling	Janet	Sales Representative	Ms.

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.

-----------
123

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

Combining statements in a batch

Return to Table of Contents

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

However, wrapping CREATE PROCEDURE around this batch will result in an error.

Changing table structure

Return to Table of Contents

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

Results:

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

Executing a string with SP_EXECUTESQL

Return to Table of Contents

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'

Result:

categoryid	categoryname	description
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

Results:

LastName	FirstName	Title	BirthDate	HireDate
Fuller	Andrew	Vice President, Sales	2/19/52	8/14/92
Buchanan	Steven	Sales Manager	3/4/55	10/17/93

LastName	FirstName	Title	BirthDate	HireDate
King	Robert	Sales Representative	5/29/60	1/2/94
Dodsworth	Anne	Sales Representative	1/27/66	11/15/94

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 baya.pavliashvili@healthstream.com.

More information from SearchSQLServer.com