Composite Transact-SQL Constructs: Batches, Scripts, and Transactions - Batches and Errors

(Page 3 of 4 )

The fact that the batch is compiled as an undivided entity has interesting implications for statements that contain syntax errors. Results will vary according to whether the syntax error occurs in a statement or in the name of a database object. If you create a batch that includes a statement containing a syntax error, the whole batch will fail to execute.

Consider the following batch:

Insert into Part (Make, Model, Type)
Values ('Toshiba', 'Portege 7020CT', 'Notebook')
Selec * from Part

It consists of two commands, the second of which contains a syntax error—a missing letter in the Select keyword. If you execute this batch, SQL Server will not compile or execute it but will return the following error:

Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 'Selec'

If you make a typo in the name of the database object (for instance, in a table or column name), the situation is very different. Note that the name of the table in the following Insert statement is incorrect:

Insert into art (Make, Model, Type) Values ('Toshiba', 'Portege 7020CT', 'Notebook')
Select * from Part

In this example, the application will notice an error and stop execution as soon as it encounters it:

Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'art'.

SQL Server executes the batch in three steps: it parses, compiles, and then executes. In the first phase, SQL Server verifies batch syntax. It focuses on the sequence of keywords, operators, and identifiers. The first batch used a statement with an error in a keyword. SQL Server picked up the error during the parsing phase.

The error in the second batch (an invalid object name) was picked up during execution. To further demonstrate this fact, let’s investigate the following example, where the error is in the second statement:

Insert into Part (Make, Model, Type)
Values ('Toshiba', 'Portege 7020CT', 'Notebook')
Select * from art

In this case, the application behaves differently:

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'art'.

Both commands are parsed and compiled, then the first command is executed, and finally the second command is canceled. Users with experience of early versions of Microsoft SQL Server remember that such a scenario would produce very different results in those early versions.

Microsoft SQL Server 2005 supports deferred name resolution (actually introduced in SQL Server 7.0). Deferred name resolution allows the server to compile Transact-SQL statements even when dependent objects do not yet exist in the database. This feature can prove to be very useful when you are creating or transferring objects from one database or server to another. You do not have to worry about dependencies and the order in which objects are created. Unfortunately, the introduction of this feature also has some strange secondary effects. In the case of the last example,

• The server has successfully compiled a batch, since the name resolution is not part of the compilation;
  
• The first command was executed without a problem; and
  
• When a problem was encountered in the second command, the server canceled all further processing and returned a runtime error.

Keep this problem in mind when writing batches. Developers in modern programming languages like Visual Basic or Visual C++ usually employ sophisticated error-handling strategies to avoid situations like this. Transact-SQL also contains programming constructs for error handling. We will explore them in the Chapter 6.

The situation could be worse. Particular runtime errors (for example, constraint violations) do not stop execution of the batch. The following case attempts to use an Insert statement to insert a value in the identity column:

Select PartId, Make + ' ' + Model Part from Part
Insert into Part (PartId, Make, Model, Type)
Values (1, 'IBM', 'Thinkpad 390D', 'Notebook')
Select PartId, Make + ' ' + Model Part from Part
Go

The result is a “partial failure”:

PartId      Part
----------- -------------------------------1           Toshiba Portege 7020CT

(1 row(s) affected)
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table
'Part' when IDENTITY_INSERT is set to OFF.
PartId      Part
----------- -------------------------------1           Toshiba Portege 7020CT

(1 row(s) affected)

In some cases, “partial success” may be tolerable, but in the real world it is generally not acceptable.

Let’s investigate a case in which several batches are written, divided by a Go statement, and executed together. Although the user has issued a single command to execute them, the client application will divide the code into batches and send them to the server separately. If an error occurs in any batch, the server will cancel its execution. However, this does not mean that execution of the other batches is canceled. The server will try to execute the next batch automatically.

In some cases, this may be useful, but in most cases, it may not be what the user expects to happen. In the following example, one column needs to be deleted from the Part table. One way to perform this action (very popular until we were spoiled;) with fancy tools like Enterprise Manager, Management Studio, or the Alter Table…Drop Column statement) would be to do the following:

1. Create a provisional table to preserve the information that is currently in the Part table.
   
2. Copy information from the Part table to the provisional table.
   
3. Drop the existing Part table.
   
4. Create a Part table without the column you want to delete.
   
5. Copy the preserved information back to the Part table.
   
6. Drop the table.

The code necessary to implement this functionality could be created in a set of five batches:

Create Table TmpPart (PartId int,
                      Make varchar(50), 
                      Model varchar(50))
GO

Insert into TmpPart (PartId, Make, Model) Select PartId, Make, Model from Part
GO

Drop Table Part
GO

Create Table Part (PartId int,
                   Make varchar(50),
                   Model varchar(50))
GO

Insert into Part (PartId, Make, Model) Select PartId, Make, Model from TmpPart
GO

Drop Table TmpPart
GO

In theory, this set of batches would work perfectly. However, there is just one problem—it doesn’t take errors into account. For example, if a syntax error occurs in the first batch, the temporary table will not be created. Part information will not be preserved in it, and when the code drops the table, the information will be lost. To observe a method that you can use to handle errors, read the next chapter.

Next: DDL Batches >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Composite Transact-SQL Constructs: Batches, Scripts, and Transactions

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

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.