Handling Exceptions

By Bill Graziano on 24 July 2000 | Tags: Application Design

naren writes "How to write The exceptions and error handling in SQL Server 7.0. Please send it as early as possible. Thank u"

I hope this is soon enough for you. I'm going to assume that you are referring to error handling within Transact-SQL scripts such as stored procedures. You can also trap database errors in your client applications such as Visual Basic, C/C++ or Active Server Pages. Each of those handles the error trapping a little differently and is specific to coding those types of applications.

We're going to cover the @@ERROR variable today. @@EROR holds the error status of the last SQL Server statements executed. For example, this code snippet,

insert authors (au_id)
values ('172-32-1176')

generates an error. One of the columns in the pubs database required a value and I sent it a NULL. The value of the system variable @@ERROR will allow us to check if an error has occurred. Our code for that might look like this,

insert authors (au_id)
values ('172-32-1176')
Select @@error

The value of @@ERROR is 515. All we really care about is that it's a non-zero value which means an error occurred. You are probably better off to declare a variable and save the return code. That code looks something like this:

declare @iError int
insert authors (au_id)
values ('172-32-1176')
Select @iError=@@error
If @iError <> 0
    Print 'Error' -- Do your error handling here

Using this method we are able to keep the result of the error. You could create variables to check the result of multiple SQL statements if you like. Every time a T-SQL statement is executed, @@ERROR is reset. If you code looked like this,

insert authors (au_id)
values ('172-32-1176')
Select @@error
Select @@error

the first select @@error would return a non-zero value. The second select @@error would return a zero. The second select statement is returning the value of @@ERROR for the statment immediately preceding it which is the select statement.

I hope this answered your question. If not you can post more specific information in a comment or resubmit it and we'll try to get you an answer.