How to Handle exceptions in SQL Server

Posted on 1998-03-11
Last Modified: 2008-03-17
When we execute queries, due to some errors , some exceptions may occur. How do we handle these ?

In Oracle it by using Exception Clause.
   illegal_delete EXCEPTION;
   PRAGMA EXCEPTION_INIT(illegal_delete,-20011);
    if (:old.sts_code !=  'CLOSED')
        if (:old.trx_sts = 'A')
          raise illegal_delete;
       end if;
    end if;
    delete cmmn_note_hdr where
    obj_type =  ('CALLMDFN') and
    obj_code = :old.call_no  ;
    delete ntfy_history where
    object_info = :old.call_no  ;
    delete cmmn_mod_history where
    table_name = 'call' and key_val = :old.call_no ;
    WHEN illegal_delete then  raise_application_error(-20011,
    'Active Call cannot be deleted ');

kindly clarify

Question by:vram
1 Comment

Accepted Solution

spiridonov earned 50 total points
ID: 1090604

check RAISEERROR Transact-SQL statement, and here is a Microsoft article on error handling:
PSS ID Number: Q44519
Article last modified on 04-25-1997
PSS database name: SQLSERVER
4.2 | 4.2

The information in this article applies to:
 - Microsoft SQL Server version 4.2 for OS/2
 - Microsoft SQL Server, version 4.2
This article discusses the different kinds of error handling in
Transact-SQL batches and stored procedures. Three classes of errors
are described, as well as additional factors concerning error handling
in nested transactions and procedures.
Syntax Errors
Syntax errors in SQL batches or procedures are detected before
execution begins; therefore, a single syntax error will cause the
entire batch or procedure to be rejected. What constitutes a
transaction and what is backed out are not issues in this case,
because execution never begins. A batch with syntax errors will
produce one or more error messages that will cause the user-defined
MESSAGE handler to be called (once for each message). The dbsqlexec
that sent the bad batch will return a status of FAIL, and a dbresults
issued to receive the results of the batch will return a status of
NO_MORE_RESULTS. The user-defined ERROR handler is used only to report
internal DB-Library (DB-Lib) errors.
Execution-Time Errors
Execution-Time errors can be either fatal or nonfatal. Attempting to
use a table or column that does not exist is fatal. Permission
violations and attempting to insert a duplicate value in a unique
index are nonfatal. (Attempting to use a nonexisting table won't get
past the syntax check, unless the table is dropped after the procedure
referencing it is created)
In either case, the user-defined MESSAGE handler will be called (once
for each message). Messages with severity 16 and higher are fatal.
If the error is fatal, uncommitted updates are backed out. Statements
not bracketed by BEGIN/COMMIT are not backed out because each one is
committed individually if it is successful. Execution is not
terminated unless the current procedure is bracketed by BEGIN/COMMIT.
If the error is nonfatal, uncommitted updates are not backed out
unless an explicit ROLLBACK is performed. Execution is not terminated
unless the batch or procedure is bracketed by BEGIN/COMMIT or a RETURN
is executed.
The dbsqlexec that sent the batch will return FAIL if the error is
fatal, and SUCCEED if the error is nonfatal. The next dbresults will
return NO_MORE_RESULTS if the error is fatal, and will return FAIL if
the error is nonfatal.
Application-Level Errors
Exceptions such as "0 rows affected" are not considered to be errors
because the SQL Server has no way of knowing whether or not such an
error is serious. Each of these kinds of conditions must be explicitly
tested by the application. A RETURN statement must be used if the
procedure is to be terminated. Otherwise, the remainder of the
statements in the procedure or batch will be executed (even if a
ROLLBACK is executed).
Exceptions produce no messages for the user-defined message handler
unless RAISERROR was executed. dbsqlexec will return SUCCEED.
dbresults will return SUCCEED unless RAISERROR was executed; if
RAISERROR was executed, dbresults will return FAIL. dbresults will
return SUCCEED even if a "not found" exception occurs.
The ROLLBACK statement does not terminate a batch or procedure; it
backs out uncommitted updates. Subsequent statements in the batch or
procedure are executed normally. This allows the application to
perform complex contingency operations if an error occurs.
Nested Logical Units of Work
Statements bracketed by BEGIN/COMMIT may be nested inside another
group of statements also bracketed by BEGIN/COMMIT. If a fatal error
occurs within the inner or outer LUW, execution is terminated and all
updates are backed out. If a nonfatal error occurs in the inner or
outer LUW, execution continues and nothing is backed out.
Nested logical units of work are meaningful only in the context of
nested stored procedures. The ability to nest logical units of work
allows stored procedures that use COMMIT/ROLLBACK to be written
without regard for whether or not they will be called by other stored
procedures that use COMMIT/ROLLBACK.
Each BEGIN TRANSACTION statement causes a counter to be incremented,
and each COMMIT statement causes the counter to be decremented. When
the counter reaches 0, a true COMMIT is performed. The current value
of the counter is in the system variable "@@TRANCOUNT".
Nested Transact-SQL Procedures
Fatal errors in nested Transact-SQL procedures are not fatal to the
parent. This allows a parent procedure to execute a contingency plan
to handle a fatal error in a subordinate procedure. It also requires
that the parent procedure check the status of @@ERROR after returning
from a subordinate. @@ERROR is set by most Transact-SQL statements;
therefore, you must be careful to RETURN before @@ERROR is changed.
The nesting of transactions resulting from nesting procedures can
cause a fatal error or ROLLBACK in the inner procedure to back out
updates performed by the parent procedure (in addition to backing out
the inner procedure's updates).
To signal to a parent procedure that a nonfatal error has occurred,
set @@ERROR in the subordinate with RAISERROR. Follow it closely with
RETURN to preserve the value of @@ERROR. In the case of fatal errors,
no RAISERROR is necessary and the RETURN is automatic.
In future releases, it will be possible to return status information
from subordinate procedures via an extension to the RETURN statement


Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question