The following text should be placed in Beginning SQL Server 2005 Programming on p. 378 right before
the section entitled “Handling Errors Before They Happen”
TRY/CATCH Blocks
Ok, so we’ve seen the way that things were done under the older style of so called error handling. It was
and is something less than elegant, but it was what we had. The tough part is that we still need to limit
ourselves to constant checking of @@ERROR if we need backward compatibility at all. The good news is
that, if you are a SQL Server 2005 only shop, you have a more traditional error handler option in the
form of a TRY/CATCH Block.
A TRY/CATCH block in SQL Server works remarkably similar to those used in any C derived language
(C, C++, C#, Delphi, and a host of others). The syntax looks like this:
BEGIN TRY
{ <sql statement(s)> }
END TRY
BEGIN CATCH
{ <sql statement(s)> }
END CATCH [ ; ]
In short, SQL Server will “try” to run anything within the BEGIN...END that goes with your TRY
block. If, and only if, you have an error condition that has an error level of 11-19 (we’ll discuss error
levels a bit later) occurs, then SQL Server will exit the TRY block immediately and begin with the first
line in your CATCH block.
Now, to test this out, we’ll make some alterations to our spInsertDateValidatedOrder stored
procedure from our previous section looking at the older style error handling. Instead of doing specific
inline tests, we are going to move the INSERT statement into the middle of a TRY/CATCH block. Now,
any error between 11 and 19 will raise an error for us without having to retest @@ERROR ourselves:
…
…
ELSE
BEGIN
SELECT @InsertedOrderDate =
CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
PRINT 'The Time of Day in Order Date was truncated'
END
/* Establish our TRY/CATCH Block */
BEGIN TRY
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
