Solved

Rolling back changes (Serializable)  within Stored proc

Posted on 2014-09-24
5
109 Views
Last Modified: 2014-09-25
Hi All,

I have a main stored procedure that calls various other stored proc's during it's execution. I'm trying to write some sort of logic so that if the main sp or any subsequent sp's called within it fails it should rollback the entire transaction.

Any sample code would be appreciated.

Thanks.
0
Comment
Question by:Samoin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40341969
<Potentially stupid question>  Have you tried using a transaction?
BEGIN TRY
  BEGIN TRAN tr
  -- do your stuff here

  -- If code execution makes it here, then good to go. 
  COMMIT TRAN tr
END TRY

BEGIN CATCH
  -- If code execution makes it here then there was a failure, kill the transaction
  ROLLBACK TRAN tr
END CATCH

Open in new window

0
 
LVL 1

Author Comment

by:Samoin
ID: 40342017
Hi Jim,

Let me give it try and I will let you know shortly.

Thanks.
0
 
LVL 1

Expert Comment

by:aneesa83
ID: 40343456
CREATE PROC TEST1

@TEST BIGINT
AS
BEGIN
SELECT @TEST+3/10
SELECT @TEST
END
;

CREATE PROC TEST2
@VAR VARCHAR
AS
BEGIN
BEGIN TRAN
EXEC TEST1 @VAR
IF( @@ERROR=0)
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK  
END
END

EXEC TEST2 A
0
 
LVL 1

Author Closing Comment

by:Samoin
ID: 40344913
Hi Jim,

I implemented your logic and it works.

Regards,
Samoin
0
 
LVL 1

Author Comment

by:Samoin
ID: 40344916
Hi aneesa83

I would prefer Transaction within a TRY....CATCH block rather then just rolling back the transaction.

Regards,
Samoin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question