Microsoft SQL Server 2012 Bible

Error Handling

If you've never encountered an error in T-SQL, you're better than most people. For everyone else, error handling is a critical and often overlooked component of T-SQL programming.

Of course, all robust programming languages provide some method for trapping, logging, and handling errors. In this area, T-SQL has a sad history, but it's made significant progress over the last couple of releases.

There are two distinctly different ways to code error handling with SQL Server:

• Legacy error handling is how it's been done since the beginning of SQL Server, using @@error to see the error status of the previous SQL statement.
• Try/catch was introduced in SQL Server 2005, bringing SQL Server into the 21st century. I highly recommend taking this approach.

Legacy Error Handling

Historically, T-SQL error handling has been tedious at best. I'd prefer to not even include this legacy method of handling errors, but you'll see it in old code, so it must be covered.

What's New with Error Handling?

SQL Server 2005 introduced TRY. . .CATCH. SQL Server 2012 rounds out that functionality (and aligns you with .NET) by introducing THROW. Now, instead of RAISERROR, you have the ability to THROW an error, which is covered it in the TRY. . .CATCH section.

The basic error information system functions, such as @@error and @@rowcount, contain the status for the previous T-SQL command in the code. This means that the legacy method of error handling must examine T-SQL's system functions ...