Transaction design and error handling in SQL Server 2000 is no easy task. Tim Chapman provides insight into designing transactions and offers a few tips to help you develop custom error handling routines for your applications.
Most iterative language compilers have built-in error handling routines (e.g., TRY CATCH statements) that you can use when designing your code. Although if you're using SQL Server 2000 you don't enjoy the luxury that iterative language developers do when it comes to built-in tools, you can use the @@ERROR system variable to design your own effective error-handling tools.
The article continues at http://uk.builder.com/programming/windows/0,39026618,39324957,00.htm