Exception handling in T-SQL/TRY…CATCH – Underappreciated features of Microsoft SQL Server

As we continue our journey through the “Underappreciated features of SQL Server”, this week are are looking at a few of the T-SQL enhancements that the community felt did not get the deserved attention. This was in response to Andy Warren’s editorial of the same name on SQLServerCentral.com.

Today, we will look at exception handling in T-SQL using the TRY…CATCH statements introduced since SQL Server 2005. Because this is now almost 6 years old, I was a bit surprised to see this in the list of underappreciated features. Hence, while I will touch upon the essential points, I will provide pointers that would help you to get started on the use of TRY…CATCH and then explore a new related feature introduced in SQL 11 (“Denali”). I am posting this on a Thursday, because I will be covering the exception handling options from SQL 7/2000 onwards and also so that it the reader gets some time to experiment with the various options presented here over the week-end.

Exception handling in T-SQL – before SQL Server 2005

Before SQL Server 2005, exception handling was very primitive, with a handful of limitations. Let’s see how exception handling was done in the days of SQL Server 7/2000:

BEGIN TRANSACTION ExceptionHandling

   DECLARE @ErrorNum INT

   DECLARE @ErrorMsg VARCHAR(8000)

   --Divide by 0 to generate the error

   SELECT 1/0

   -- Error handling in SQL 7/2000 

   -- Drawback #1: Checking for @@Error must be done immediately after execution fo a statement

   SET @ErrorNum = @@ERROR

   IF @ErrorNum <> 0 

   BEGIN

      -- Error handling in SQL 7/2000 

      -- Drawback #2: When returning an error message to the calling program, the Message number,

      --              and location/line# are no longer same as the original error.

      --              Explicit care must be taken to ensure that the error number & message

      --              is returned to the user.

      -- Drawback #3: Error message is not controlled by the application, but by the T-SQL code!

      -- (Please note that there is only one 'E' in RAISERROR)

      SELECT @ErrorMsg = description FROM sysmessages WHERE error = @ErrorNum

      RAISERROR ('An error occured within a user transaction. Error number is: %d, and message is: %s',16, 1, @ErrorNum, @ErrorMsg) WITH LOG

      ROLLBACK TRANSACTION ExceptionHandling

   END

IF (@ErrorNum = 0)

   COMMIT TRANSACTION ExceptionHandling

Read more: Beyond Relational

Posted via email from Jasper-net