How to check if an error occured Inside Transaction or outside ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

Hello ,
I have created a stored Procedure and have used Transaction Inside Try Catch block. I have put some lines of code before transaction to. if an error occurs it will be caught by the catch block. Now if i put rollback tran inside catch and error occurs before tran , then it is a mistake. how will i know if the error occurred inside tran ?

For example here is a sample procedure

Create Procedure Test
@Input varchar(10)
As
Begin
 Begin Try
    Declare @Value int;
    Set @Value = @Input

     Begin Tran
      Insert into abc value (@Value);
     
     Commit Tran
 End Try

 Begin Catch
      Rollback Tran
 End Catch     

End

<pre>

Here an error will occur because @Value is of type int and i am assigning it a string value.
SO Execution will pass on to the catch block. There i have put rollback tran, but the transaction nerver started so how can it rollback ??

Posted 16-Sep-15 19:53pm

Riya-Pandey

Updated 16-Sep-15 20:00pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-09-16T20:16:00

It's because a Transaction is intended to work with more than one command: inserting related data into to tables for example.
If either one of the inserts fails, you don't want any of the data from either in the DB because it wouldn't have it's matching data. Think about an invoice: you have one table containing the invoice header (customer, invoice number, date) and a separate table containing a row for each of the individual products the customer bought. If the header fails (because the date is 31st Feb 2111111 perhaps) then you don't want the product rows in your DB because they don't have a header to join them together. Equally, if one of the products doesn't exist, you don't want the header to generate an invoice either, because some of the information is missing.

So you use a Transaction around the whole set of INSERT statements:

SQL

Begin Try
    Begin Tran
     INSERT INTO Invoices (InvoiceNumber, CustomerID, InvoiceDate) VALUES (@IN, @CID, @IDT);
     INSERT INTO InvoiceLines (InvoiceNumber, ProductID) VALUES (@IN, 1234)
     INSERT INTO InvoiceLines (InvoiceNumber, ProductID) VALUES (@IN, 1235)
    Commit Tran
End Try

Begin Catch
     Rollback Tran
End Catch

And when anything fails, the ROLLBACK undoes them all.