Transactions Locks «Prev  Next»

Lesson 11 Error testing
Objective Test for Error Handling

Test for Error Handling and Transaction Locking

Purpose of Error Handling and SQL-Server Business Rules

Error handling is very important within transactions. Without handling errors, you would not know when to roll back transactions. It is important to know that if any statement in your transaction produces a severe error such that the transaction cannot be completed successfully, SQL Server automatically rolls back the transaction.

Testing for errors

Assuming that the transaction was not automatically rolled back, you need to test for errors in your transactions. There are system-level global variables that are populated with values by SQL Server for this purpose.
These are the variables that you can use:
  1. @@ERROR:Gives the SQL Server error number of the last executed Transact-SQL statement. If there was no error, @@ERROR will be 0.
  2. @@TRANCOUNT:Indicates the number of currently open transactions for a session. Every call to BEGIN TRANSACTION increments @@TRANCOUNT by 1. END TRANSACTION decrements @@TRANCOUNT by 1. @@ROLLBACK TRANSACTION resets @@TRANCOUNT to 0, unless a savepoint name is specified. In this case, @@TRANCOUNT is unaffected.
  3. @@ROWCOUNT:Indicates the number of rows that are affected by the last executed Transact-SQL statement.

When errors occur, it is a good idea to generate an error message and send it back to the application that started the transaction. This is done by using the RAISERROR Transact-SQL statement. It follows this general syntax:
1) MESSAGE_ID is a user defined message number. All user-defined messages are stored in the sysmessages table. 2) message_string is a string of text to report back to the calling application or procedure. 3) severity is the category level of error. 4) State is an arbitrary value that you select (from 1 to 127) to further identify the state of your error. 5) argument is one or more arguments that can be used in formatting the message 6) option is one of the following : 1) Logs the error in the application log and the server log

Handling Errors
Because the RAISERROR Transact-SQL statement takes the last two arguments as the database ID and the database name, you can obtain the current database ID and the current database name by using the DB_ID() and DB_NAME() functions, respectively. However, you cannot use these directly in the RAISERROR Transact-SQL statement, so you must declare variables of the type that are returned by these functions, call those functions, and assign the return values to the variables that you declared. You can do that with this simple code (which you can use consistently in your procedures):

DECLARE @DBID SMALLINT
DECLARE @DBNAME NVARCHAR(128)
SET @DBID = DB_ID()
SET @DBNAME = DB_NAME()

Then you can use the @DBID and @DBNAME variables when needed, like this:
RAISERROR ("Error Text Here", 16, 1, @DBID, @DBNAME)

Testing transactions

It is a good idea to implement some form of error handling to test if the individual Transact-SQL statements that are wrapped within the transaction fail. There are as many different ways to do this as you can think of. Here is one example of how you might implement error handling and rollback transactions when errors occur:

DECLARE @DBID SMALLINT
DECLARE @DBNAME NVARCHAR(128)
SET @DBID = DB_ID()
SET @DBNAME = DB_NAME()
BEGIN TRAN
UPDATE Finance
SET Salary = 100000
WHERE EmployeeID = 101
select @@rowcount
select @@trancount
IF @@ROWCOUNT = 0 or @@TRANCOUNT = 0
    BEGIN
 /* an error occurred in the first statement */
 ROLLBACK TRAN
        RAISERROR ("Finance failed - 
  Transaction rolled back",
  16, 1, @DBID, @DBNAME)
        RETURN
    END
UPDATE FinanceHistory
SET LastSalaryUpdate = "07/04/99"
WHERE EmployeeID = 101
IF @@ROWCOUNT = 0 or @@TRANCOUNT = 0
    BEGIN
 /* an error occurred in the second statement */
 ROLLBACK TRAN
        RAISERROR ("FinanceHistory failed - 
  Transaction rolled back", 16, 1, @DBID,
 @DBNAME)
        RETURN
    END
/* everything succeeded - commit the transaction */
COMMIT TRAN


Notice in the code above that not only does the code rollback the transaction if either of the two Transact-SQL statements contained within fail, but a detailed error message is displayed in either case. This way debugging the transaction is easy. This could have been taken one step further by displaying a different error message based on @@ROWCOUNT being 0 (indicating that the condition set in the WHERE clause produced no results) or the @@TRANCOUNT being 0 (indicating that the transaction had already been rolled back or committed by another process). The next lesson reviews the information covered in this module.

Handling Transaction Errors - Exercise

Click the Exercise link to practice handling errors within your transactions.
Handling Transaction Errors - Exercise