Error int) As BEGIN TRANSACTION Delete from

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. Previous versions of SQL Server 2005 RAISEERROR is used.
Starting with SQL Server 2005 we can capture and handle exceptions using two
statements TRY and CATCH.

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


order now

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.

The Throw statement is introduced in SQL SERVER 2012. This
Throw statement behaves similarly to RAISEERROR statement. 

Each TRY CATCH must be inside a single batch, stored
procedure or trigger.

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.

Example of using TRYCATCH in procedure for deleting the
records.

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.

Eg:

Create procedure deteleemployee(@employeeid int)

As

Delete from employeephonenos

Where employeeid= @employeeid

 

Delete from employee

Where employeeid= @employeeid

 

From the above procedure we want to statement to be either
fail or both should be succeed  so we
need to wrap both statements into transaction

Create procedure deteleemployee(@employeeid int)

As

BEGIN TRANSACTION

Delete from employeephonenos

Where employeeid= @employeeid

 

Delete from employee

Where employeeid= @employeeid

 

END

 

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.

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 ‘

THROW

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 along with the message from the
THROW command.

In this way TRY and CATCH block is used for error handling
in SQL SERVER. 

x

Hi!
I'm Alfred!

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

Check it out