SQL Server batch rules

SQL Server batch rules

Baya Pavliashvili, Contributor

 
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
  

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in January 2006

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

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

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

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

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

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

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

 

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top