Exception Handling in SQL Server 2000 and 2005
(Page 1 of 5 )
This article mainly discusses and compares the features of exception handling in Microsoft SQL Server 2000 with the same features in SQL Server 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.
Introduction to error handling
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:
Drop table [dbo].[emp]
GO
CREATE TABLE [dbo].[emp] (
[empno] [int] NOT NULL ,
[ename] [varchar] (50),
[sal] [float] NULL ,
[deptno] [int] NULL
) ON [PRIMARY]
GO
droptable [dbo].[error_log]
GO
CREATE TABLE [dbo].[error_log]
(
[LogDate] [datetime] NULL ,
[Source] [varchar] (50),
[ErrMsg] [nvarchar] (255) ,
[Remarks] [varchar] (50)
)
GO
