Handling Exceptions

By Bill Graziano on 24 July 2000 | 1 Comment | 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.

Discuss this article: 1 Comment so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Tricky Logic Using Group by (0 Replies)

integrity problem (3 Replies)

Is this safe to do? (2 Replies)

Stored Procedure for paging 2000 vs 2005 (2 Replies)

select columns in rows (3 Replies)

Turning Rows into Columns (4 Replies)

How to find clashes (1 Reply)

how to get Distinct id with same value (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -