email article printer friendly get the code more resources

Take Advantage of New T-SQL Features
New T-SQL keywords implement TRY...CATCH error handling, pivot aggregate values to create crosstab tables, rank and partition rowsets, generate temporary in-memory tables with common table expressions, and more.
by Roger Jennings

July 29, 2005

Technology Toolbox: VB.NET, SQL Server 2005, Visual Studio 2005/Visual Basic Express Edition, ADO.NET 2.0

SQL Server 2005's five-year gestation period gave Microsoft's development team the opportunity to implement an extraordinary number of new features that DBAs and database developers will find useful.

Recent articles have covered SQL Server 2005's native XML data type, SQL Server 2005 Express Edition, and column-level or cell-scoped encryption (see Additional Resources). In this article, I'll describe new T-SQL keywords that let you substitute TRY...CATCH structure for the @@ERROR function, pivot aggregate rowsets to generate crosstab reports, add row numbers with ranking and windowing functions, and create temporary in-memory tables with common table expressions. I'll also demonstrate new modifiers that greatly enhance SQL Server 2000 FOR XML queries. Most T-SQL batch statement examples are included in this article's sample code. The sample code requires installing the Northwind sample database to your SQL Server 2005 or SQL Express instance (see Additional Resources). By default, expanding the ZIP file creates a \TSQL2005 folder to contain the sample T-SQL scripts, which you can open and execute in SQL Server Management Studio or SQL Express Manager.

New BEGIN TRY ... END TRY and BEGIN CATCH ... END CATCH blocks let you emulate structured exception handling in T-SQL batch queries and stored procedures. The five ERROR_* functions return much more information about the error than the @@ERROR function's error number:

BEGIN TRY
   -- Batch statements;
END TRY
BEGIN CATCH
   -- Error-handling statements, typically
   SELECT ERROR_NUMBER() AS ErrorNumber,
      ERROR_PROCEDURE AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The BEGIN CATCH statement must be the next line after the END TRY instruction. You can nest TRY...CATCH blocks with this structure:

BEGIN TRY
   -- Outer-level statements
END TRY
BEGIN CATCH
   -- Outer-level error-handling statements
   BEGIN TRY
   -- Inner-level statements
   END TRY
   BEGIN CATCH
   -- Inner-level error-handling statements
   END CATCH
END CATCH

Run this script to illustrate T-SQL's new error-handling functionality:

BEGIN TRAN
GO
BEGIN TRY
   -- Causes a constraint violation on the 
   -- Order Details table.
   DELETE FROM Products 
      WHERE ProductID = 15
   COMMIT TRAN
END TRY
BEGIN CATCH
   ROLLBACK TRAN
   SELECT ERROR_NUMBER() AS ErrorNumber,
      ERROR_LINE() AS ErrorLine,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_MESSAGE() as ErrorMessage
END CATCH
GO

Executing the script (TryCatchBlocks.sql) throws this error message: "The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "Order Details", column 'ProductID'."