Error TRY and CATCH blocks like Exceptions provide

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

x

Hi!
I'm Freda!

Would you like to get a custom essay? How about receiving a customized one?

Check it out