Solved

Rolling back changes (Serializable)  within Stored proc

Posted on 2014-09-24
5
104 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
Comment Utility
<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
Comment Utility
Hi Jim,

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

Thanks.
0
 
LVL 1

Expert Comment

by:aneesa83
Comment Utility
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
Comment Utility
Hi Jim,

I implemented your logic and it works.

Regards,
Samoin
0
 
LVL 1

Author Comment

by:Samoin
Comment Utility
Hi aneesa83

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

Regards,
Samoin
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

10 Experts available now in Live!

Get 1:1 Help Now