?
Solved

Rolling back changes (Serializable)  within Stored proc

Posted on 2014-09-24
5
Medium Priority
?
114 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 66

Accepted Solution

by:
Jim Horn earned 1500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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