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.


Related Articles

Application Locks (or Mutexes) in SQL Server 2005 (7 January 2008)

What I Wish Developers Knew About SQL Server (Presentation) (11 October 2007)

Multiple Active Result Sets (MARS) – Transactions and Debugging (17 June 2007)

Multiple Active Result Sets (MARS) (3 April 2007)

How SQL Server 2005 Enables Service-Oriented Database Architectures (8 September 2006)

Presentation: What I Wish Developers Knew About SQL Server (17 November 2005)

GeoCoding with SQL Server and Google (8 August 2005)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Other Recent Forum Posts

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (5h)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (5h)

Filter query base on date field (4d)

Pull results from a stored procedure (4d)

Help With Query To Count Number of Pets & Vaccinations (5d)

Connect sql off network (5d)

Moving Registered Servers to a New Instance (9d)

CU 31 for 2017 Will Not Install (9d)

- Advertisement -