Solved

Rolling back changes (Serializable)  within Stored proc

Posted on 2014-09-24
5
106 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now