This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Error Handling in T-SQL: From Casual to Religious

Dejan Sunderic

Most of us would agree that experienced programmers tend to be more adept at (and perhaps even more "religious" about) error handling than rookies. VB and C/C++ programmers are so spoiled by the error-handling tools in their IDEs that they sometimes forget good old-fashioned "roll your own" error handling. In this article, Dejan Sunderic provides some guidance for both DBAs and database application developers.

T-SQL is rather laconic (critics would say feature-poor)–especially when it comes to error handling, and DBAs, who tend to write a lot of rather straightforward scripts, are often guilty of neglecting the kind of robust error handling that applications need. And to complicate matters, logic that’s fine in standard languages like VB or C/C++ might not even work in T-SQL. For example, it might make sense to let a T-SQL script continue to run even after an error occurs–assuming the error is "noncritical."

	Another typical error that T-SQL tyros often make is to check the value of global variables and then to try to return or process it:

  Update 	…
If @@Error <> 0 
Begin
   Select 'Unexpected error occurred: ', @@Error
   Return @@Error
End

&#9;Although code like this is common in VB, in this case, the stored procedure will return 0 as the error number. That’s because SQL Server sets the value of @@Error variable after each statement. Assuming successful completion of the If statement, the final value of @@Error will be 0.

Why bother?

For many, the question is, "Why bother?" Let’s look at a simple example:

  Begin transaction
   Update…
      Set…
      Where…
   Update…
      Set…
      Where…
Commit transaction

&#9;Most DBAs would cringe at code like this because we’ve learned to make transactions as granular as possible. Inexperienced T-SQL programmers, however, might not be familiar with transaction processing and thus not realize that, if errors occurred while processing the second UPDATE, SQL Server would still unconditionally commit the first (successful) UPDATE. (Imagine the classic ATM example where funds were debited from one account, but never credited to the second account.)

"Casual" error handling

Intermediate programmers who understand the nature of transaction processing aren’t likely to make that kind of error. They might write code like this:

  Begin transaction 
Update …
If @@error <> 0 
Begin
   Select 'Unexpected error occurred!'
   Rollback transaction
   Return 1
End
Update …
If @@error <> 0 
Begin
   Select 'Unexpected error occurred!'
   Rollback transaction
   Return 1
End
Commit Transaction

&#9;However, this solution contains a lot of repetitive code. The logical next step is to group some of the code into a generic error-handling procedure such as this:

  Begin transaction
Update ….
If @@error <> 0 goto ERR_HANDLER
Delete …
If @@error <> 0 goto ERR_HANDLER
Commit Transaction
…
Return 0
ERR_HANDLER:
   Select 'Unexpected error occurred!'
   Rollback transaction
   Return 1

&#9;Although this is certainly an improvement, it still doesn’t deal with all issues that need to be handled.

A more coherent (religious) solution

Let’s try to develop a generic, yet comprehensive solution for error handling in T-SQL. The basic idea is that all SQL statements inside a stored procedure should be covered with error-handling code. Any time an unexpected error occurs, a stored procedure should stop further processing. Furthermore, not only will this impact the stored procedure itself, but it will also impact any stored procedure(s) that have called it.

&#9;The basic element of the solution is that all SQL statements should be followed with a statement that reads the contents of @@Error variable to determine if the statement completed successfully.

  Declare @ErrorCode int
Select @ErrorCode = @@Error
If @ErrorCode = 0
Begin
   --Some statement
   Update …
   Select @ErrorCode = @@Error
End
If @ErrorCode = 0
Begin
   --Another statement
   Insert …
   Select @ErrorCode = @@Error
End
If @ErrorCode = 0
Begin
   --Another statement
   Update …
   Select @ErrorCode = @@Error
End
…
Return @ErrorCode

&#9;At the end, the stored procedure should return the value of @ErrorCode variable to the calling stored procedure or script. If errors have occurred, this might be used to notify the calling procedure that there was a problem.

Nested stored procedures

Okay, but what about nested stored procedures? Well, calls to stored procedures should treat return values as error codes, as follows:

  If @ErrorCode = 0
Begin
   execute @ErrorCode = MyStoredProcedure parm1, param2…
End

&#9;This system works like a cascade and will stop all further processing in whole sets of nested stored procedures.

Interfacing other environments

This structure is very useful even in cases when a stored procedure was called from some other programming environment, such as VB or Visual C++. The return value of a stored procedure can be retrieved and an error can be handled on that level as well.

  conn.Open "provider=sqloledb;data source=sqlserver;" _
   + "user id=sa;password=;initial catalog=pubs"
cmd.CommandText = "exec test_proc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("RetVal", _
   adInteger, adParamReturnValue)
Set rs = cmd.Execute()
lngReturnValue = rs(0)
If lngReturnValue <> 0 Then
   Begin
      MsgBox "Procedure has failed!"
      Exit Sub
   End
Else
   MsgBox "Procedure was successful!"

Other global variables

There are cases when you wish to read the value of some other global variables immediately after the statement, and you should handle them with the same select statement that reads @@Error. For example, you often require something like this when you’re using identity columns.

  Insert …
Select @id = @@identity,
   @ErrorCode = @@Error

Transaction processing

Transaction processing can be perfectly integrated with this solution. At the beginning of a stored procedure (or transaction), the developer should add the following:

  Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
   Select @TransactionCountOnEntry = @@TranCount
   BEGIN TRANSACTION
End

&#9;At the end of the procedure/transaction, the developer should complete the transaction as follows:

  If @@TranCount > @TransactionCountOnEntry
Begin
   If @ErrorCode = 0
      COMMIT TRANSACTION
   Else
      ROLLBACK TRANSACTION
End

&#9;The solution will also perform well in the case of nested stored procedures. All procedures will be rolled back using the same cascading mechanism. Here, the local variable @TransactionCountOnEntry is used to track the number of opened transactions upon the entry of a stored procedure. If the number was unaffected inside the stored procedure, there’s no reason to either commit or rollback inside the procedure.

Conclusion

Critics might have objections to the proposed solution. For example, it adds a lot of code in the script, and it would be improved if the developer can "hide" it to place statements doing "real" processing in the forefront. But the solution’s real value is that it will permit code in stored procedures to work in a uniform manner and developers to know what to expect when the unexpected occurs.

Dejan Sunderic is currently working as the principal consultant for the Toronto-based Trigon Blue, Inc. His specialty is development and project management of B2B eCommerce, OLTP, and decision-support systems. dejans@hotmail.com.

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the June 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.