Composite Transact-SQL Constructs: Batches, Scripts, and Transactions
(Page 1 of 4 )
If you want to learn more about Transact-SQL statements, this article gets you off to a good start. The first of a multi-part series, it covers batches. It is excerpted from chapter five of the book
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).
Transact-SQL (T-SQL) statements can be grouped and executed together in a variety of ways. They can be
- Compiled as a part of a stored procedure, user-defined function, or trigger
- Written and executed individually or in groups from client utilities in the form of batches
- Grouped and stored in external script files that can be opened and executed from various client utilities
- Grouped in transactions that succeed completely or fail completely
This chapter discusses batches, scripts, and transactions.
It is not necessary to run examples from the text against the Asset5 database, but if you do, you must first make sure that the database contains the following table by executing the following script against the Asset5 database:
Create Table Part(PartId int identity,
Make varchar(50),
Model varchar(50),
Type varchar(50))
This table is used to illustrate the concepts discussed in this chapter. Some of the changes are destructive, so existing tables such as Equipment will not be used, which may be needed for other purposes later.
Batches
A batch is a set of Transact-SQL statements that are sent to and executed by SQL Server as a single unit. The most important characteristic of a batch is that it is parsed and executed on the server as an undivided entity. In some cases, batches are created implicitly. For example, if you execute a set of Transact-SQL statements from Query Analyzer, the program will treat that set as one batch and do so invisibly:
Insert Into Part (Make, Model, Type)
Values ('Toshiba', 'Portege 7010CT', 'notebook')
Insert Into Part (Make, Model, Type)
Values ('Toshiba', 'Portege 7020CT', 'notebook')
Insert Into Part (Make, Model, Type)
Values ('Toshiba', 'Portege 7030CT', 'notebook')
Some tools, such as the Query window in Management Studio and SQLCMD in SQL Server 2005, and Query Analyzer, osql, and isql in earlier versions of SQL Server, use the Go command to divide Transact-SQL code into explicitly set batches. In the following example, the code for dropping a stored procedure is in one batch and the code for creating a new stored procedure is in another. The batch is explicitly created using the Go command.
If Exists (Select * From sysobjects
Where id = object_id(N'[dbo].[prPartList]')
And OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop Procedure [dbo].[prPartList]
Go
Create Procedure prPartList
As
Select * from Part
Return 0
Go
In a Query window of Management Studio, you can highlight part of the code and execute it. Management Studio treats the selected piece of code as a batch and sends it to the server and ignores the rest of the code (see Figure 5-1).
In other utilities and development environments, batches may be divided in some other manner. In ADO, OLE DB, ODBC, and DB-Library, each command string prepared for execution (in the respective object or function) is treated as one batch.
NOTE
In SQLCMD (and command line tools in earlier version of SQL Server), the Go command has a different function. It is also a signal to the tool to start executing the code entered so far.
Next: Using Batches >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
