How to Handle exceptions in SQL Server

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Victor SpiridonovCommented:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.