Exception Handling in SQL Server 2000 and 2005

Basically, in Microsoft SQL Server 2000, there exists no structured exception handling.  We need to depend on @@ERROR for any errors that occur.  Microsoft SQL Server 2005 has been enhanced in such a way that developers program more powerful and error resistant SQL code with structured exception handling.

 

In this article, I shall provide some samples in both SQL Server 2000 and SQL Server 2005.  I shall also give explanations on both of the approaches by comparing each of them.  I've tried to keep this article looks as simple as possible to get beginners off to a good start.

 

I am assuming that the readers of this article will have some knowledge of RDBMS along with some exposure to SQL Server 2000.

 

 

Before going to exception handling, let us first determine all the possible ways to get errors. Errors may occur in T-SQL (of course not only in T-SQL) in several possible ways, including hardware failures, network failures, bugs in programs, out of memory and for several other reasons.  We may not know which error has been raised at what moment.  But we need to handle all such errors and provide some meaningful messages to the user (instead of making the user horrified with error messages that are impossible to understand).

 

An exception is generally a runtime error which gets raised by SQL Server runtime when a T-SQL block is in the process of execution.  Handling the exception is something like trapping the error (or exception) and inserting that error into the error_log table including date, error message, and other details. Storing error messages in the error_log table makes it easy to trace for future maintenance. 

 

It doesn’t mean that errors are in the table only for maintenance; we can take certain actions (programmatically) when an error occurs. Error handling is a very monotonous task and we should make it as simple as possible. If error handling is too complex, bugs might creep into the error handling and should be tested after each statement.

 

Another special case is the use of transactions. We need to issue a “ROLLBACK TRANSACTION” to undo a transaction when an error creeps in.

 

Before going into the examples, you need to have the following simple tables (“emp” and “error_log”) created in the Northwind (or any other) database as follows:

 

droptable [dbo].[error_log]

 

 

 

 

Let us try to recollect error handling used in our previous experiences with SQL Server 2000 or before. We can observe that this job is monotonous in SQL Server 2000 because for every statement a local value must be stored, which decreases the clarity of the code and increases the complexity and even the size of the code. 

 

Let us consider the following code.

 

 

To execute the above program, you need to issue the following statement in query analyzer:

 

 

I shall explain the above code in the next section.

 

 

In this section, I shall explain to you the source code I presented in the previous section. I shall explain it part by part. Let us start with the following:

 

The above line simply specifies the name of the stored procedure to create, namely “sp_emp_insert.”

 

 

 

The above are the parameters which hold all values passed (when execution) to the stored procedure “sp_emp_insert.”

 

 

The above statement simply declares a variable @Error of type integer to hold the error which may occur, while executing the stored procedure.

 

Here, we need to understand and must be familiar with word transaction, which means “process must be executed either in its entirety or not at all.” Transactions should either end with a COMMIT or ROLLBACK statement. If it ends with a COMMIT statement, all the changes made to the database are permanent. If the transaction fails, or ends with a ROLLBACK, none of the statements takes effect. The above INSERT statement tries to insert a row of values into the table “emp” as part of transaction.

 

“@@ERROR” is simply a built-in global variable mainly used to know the status of errors within our code.  It contains the error id produced by the last SQL statement.  If no error occurs with the latest DML statement, the value of @@ERROR would be zero.  The value of @@Error must be assigned to a user-defined variable (like @Error) before proceeding to any other work.  It is a rule of thumb in SQL Server 2000.

 

According to the above IF condition, if there exists a non-zero value in @Error, some error has occurred and I wanted to log it immediately. Once the “goto” gets executed, the flow of execution immediately jumps (thereby skipping all the statements in between) to the statement titled “LogError.”

 

 

According to above statements, if no error exists, we proceed with committing (or making changes permanent) and immediately jump to the end of the procedure.

 

 

The above set of statements gets executed if and only if any error gets raised.  The set of statements include the rolling back issue (which cancels the transaction).  And we finally log it using the following INSERT statement based on the error information retrieved by the above SELECT statement.

 

 

As we have seen the basics of error handling in SQL Server 2000, we shall now try to achieve the same in SQL Server 2005 (but with greater flexibility and structured exception handling).

 

Create the same two tables in SQL Server 2005 as specified in the first section. Now, let us modify the previous procedure with a better approach in SQL Server 2005.  What follows is the modified code.

 

 

      @empno      int,

      begin transaction

      insert into emp (empno,ename,sal,deptno)

            values (@empno,@ename,@sal,@deptno)

      commit transaction

      rollback transaction

      insert into error_log (LogDate,Source,ErrMsg)

            values (getdate(),'sp_emp_insert',error_message())

 

The execution is very similar to that of the previous code.  You simply issue and execute the following statement in SQL Server Management Studio:

 

 

The execution again is very similar to what we've previously seen. You simply issue and execute the following statement in SQL Server Management Studio.  The explanation for the above code is given in the next section.

 

 

I hope everybody can understand the definition of the CREATE PROCEDURE (as discussed in previous sections).  Further proceeding we have the following.

 

      begin transaction

      insert into emp (empno,ename,sal,deptno)

            values (@empno,@ename,@sal,@deptno)

      commit transaction

 

This is a bit different from the previous sample.  The statements between “begin try” and “end try” will be simply TRIED by SQL Server run time to execute. If the statements between “begin try” and “end try” get executed successfully without any errors, the transaction is saved successfully with the help of COMMIT TRANSACTION.

 

      rollback transaction

      insert into error_log (LogDate,Source,ErrMsg)

            values (getdate(),'sp_emp_insert',error_message())

 

If any error occurs while executing the statements between “begin try” and “end try,” the flow of execution automatically gets jumped to the “catch” block. Within the “catch” block (or between “begin catch” and “end catch”), we simply rollback (in other words, cancel) the transaction and finally log the error to the “error_log” table using a simple INSERT statement (as above).  Another flexibility in SQL Server 2005 is the “error_message().”  It gives us the immediate error message that occurred.

 

From the above code, you can observe that we are trying to eliminate all “goto” statements and make it “structured.”  This really makes our script easily understandable to the depth of any number of exceptions.