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

Checkmark for guaranteed SR = WR (12h)

How to connect to git in SQL Server 2016/2017 without using any third party tool (12h)

Sql restart (17h)

Excel column wise data save in rows (1d)

Date timzone conversion (2d)

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2d)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (2d)

Two records into a single record? (2d)

- Advertisement -