BEGIN Commit trans

Question

BEGIN Commit trans

JKSQL

SSCrazy Eights

Points: 8481
More actions
January 19, 2009 at 11:30 am

#126771

So I know how to do a begin and commit with rollback on one tran or a try catch block. This is a long script and I do not want to test each statement. I just want it to rollback anything that was done. Is there an easy way to do this without testing each case. Below are two examples
[Code]
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;
GO
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 14;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 15;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 16;
---Now Force blow up
11111111
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 17;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 18;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 19;
GO
COMMIT TRANSACTION;
GO
[/Code]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

What is it you're testing for? Execution errors or whether it actually deleted anything?

If you're looking at Execution-type errors, I'd think BEGIN TRY...END TRY would be your friend (BOL has some good examples). Otherwise - give a little details on what you're trying to trap.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

Yup I am just looking for exceptions. That solutions works. Thank you. I knew I was not putting something together

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

SET XACT_ABORT ON

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)