Solved

How to Handle exceptions in SQL Server

Posted on 1998-03-11
1
412 Views
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.
eg:
 CREATE OR REPLACE TRIGGER TBD_CA_CALL
BEFORE DELETE ON CALL FOR EACH ROW
DECLARE
   illegal_delete EXCEPTION;
   PRAGMA EXCEPTION_INIT(illegal_delete,-20011);
begin
    if (:old.sts_code !=  'CLOSED')
    then
        if (:old.trx_sts = 'A')
        then
          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 ;
    EXCEPTION
    WHEN illegal_delete then  raise_application_error(-20011,
    'Active Call cannot be deleted ');
end;

kindly clarify
bye
Ram

0
Comment
Question by:vram
1 Comment
 
LVL 7

Accepted Solution

by:
spiridonov earned 50 total points
Comment Utility

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
 
OS/2 WINDOWS
 

======================================================================
----------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft SQL Server version 4.2 for OS/2
 - Microsoft SQL Server, version 4.2
----------------------------------------------------------------------
 
SUMMARY
=======
 
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.
 
MORE INFORMATION
================
 
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.
 
ROLLBACK
--------
 
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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now