Error A to another account B. For this

Error control is important in database programming
since it gives the ability to roll back the transactions in response to the
problems occurred. By default, SQL server doesn’t stop transactions due to
exceptions which can result invalid data. Error handling for TSQL is similar to
the exception handling in the java language. A group of TSQL statements can be
written in a TRY block. If an error occurs TRY statement capture the
exception.  If exception occurs the try
block the control is passed to CATCH block.

There are lot of benefits with TRY and CATCH blocks
like Exceptions provide a clean way to check for errors without cluttering
code, exceptions can be seen by the programmer and checked during the
compilation process. Most of the people use try/catch block in stored procedure
In order to log any exceptions that occurs in database since it is good for the
applications which require integrity.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

 Below I listed
4 best practices while implementing the TRY CATCH

Best
Practice 1: 
Each TRY CATCH must be inside a single batch, stored procedure or
trigger.

TRY block must be followed by a CATCH block. TRY CATCH
will not handle the errors which cause the database engine to terminate. TRY
CATCH does not work for compilation errors.

Eg: create procedure selectdata

AS

BEGIN

BEGIN TRY

Select * from  abc

END TRY

BEGIN CATCH

END CATCH

END

Best
Practice 2: TRY CATCH BLOCK with ROLLBACK AND COMMIT

Incomplete transactions shouldn’t be committed. If one
of the statements produce error than We can roll back the transaction and erase
all data starting from the transaction. If there is no error then we can COMMIT
the transactions.

Example of using TRY CATCH block in stored
procedure while modifying the data in the tables.

Let us assume that I have a bank table and I want to
transfer the money 5000 from  account A
to another account B.

For this I have to first  withdraw the amount  from account A and credit it to the account B
so

I have to write

UPDATE account SET
total=total+5000.0 WHERE account_id=1337;

UPDATE account SET
total=total-5000.0 WHERE account_id=45887;

These
two statements should execute at the same time i.e; both have to be performed
or both should fail. If any of the one is succeed without other than data is
not correct.

 

So for this we can use transaction and also a try
catch block for the commit and roll back

Create procedure transfer(@accA int , @accB int)

As

BEGIN TRY

BEGIN TRANSACTION

UPDATE account SET
total=total+5000.0 WHERE [email protected];

UPDATE account SET
total=total-5000.0 WHERE [email protected];

 

–if there is no error

COMMIT

END TRY
BEGIN CATCH

ROLLBACK

Print ‘Cannot done the transaction ‘

END CATCH

END

When we execute this procedure if the two commands
execute successful then transaction Commit else it goes to CATCH block and
performs rollback then print cannot done the transaction.

 

BEST
PRACTICE 3: USING TRY-CATCH with XACT_ABORT

For good error handling we need both TRY-CATCH and SET
XACT_ABORT ON. The option XACT_ABORT is needed for more reliable error and
transaction handling. SET XACT_ABORT ON instructs SQL SERVER to roll back the
entire transaction and abort the batch when a runtime error occurs. It also specifies what action SQL SERVER should tale
following run-time errors.

SET XACT_ABORT OFF is the default one it indicated
that only the T-SQL statement which raised the error is rollback and the
transaction continues.

 

BEST
PRACTICE 4: USING TRYCATCH with RAISEERROR OR THROW

RAISEERROR and THROW statements are used to raise an
error in SQL SERVER.  THROW statement is
easy to use compared to RAISE ERROR.  
BOTH can be used in CATCH block to raise the error. By just specifying
the THROW statement without any parameters and RAISEERROR with parameters. IF
we want to raise even system exception it is better to use RAISEERROR instead
of THROW.

 

Example:  USING TRY CATCH USING THROW

Let us assume that I have two tables, employee and
employeephonenos both are related to each other and I have procedure called
Deleteemployee.  The delete employee has
2 statements to delete records in employeephonenumbers which are related to
employee table.

Here suppose if we get the error in deleting the
records we can handle the situation using the try catch and throw
statements 

Create procedure deteleemployee(@employeeid int)

As

BEGIN TRY

BEGIN TRANSACTION

Delete from employeephonenos

Where employeeid= @employeeid

 

Delete from employee

Where employeeid= @employeeid

 

–if there is no error

COMMIT

END TRY
BEGIN CATCH

ROLLBACK

Print ‘Any message or transaction rollback’

THROW

END CATCH

END

 

In the Try block a transaction is started and the two
DELETE statements are performed. IF both are succeed then COMMIT will be
executed and the transaction will be committed. If , either one  of the delete statement gives an error then
control goes to the CATCH block .In the catch block the transaction will be
roll back and we will  get the message
transaction rollback and the Throw gives the message what type of error it is.