troubleshooting Question

executing nested stored procedures within the transaction

Avatar of venkataramanaiahsr
venkataramanaiahsr asked on
Microsoft SQL Server 2008
5 Comments1 Solution451 ViewsLast Modified:
I have two sp - sp1 and sp2

Sp1 is called from the client program

SP1 code
Begin Try
 
   Begin Transaction

    1....
    2....

                     if @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
                              
                                 Begin
                                      ROLLBACK
                                     Select 'Mismatch in AmtRefund_Receipt and
                                                 AmtRefund_BillDetail Cannot Refund Bill... Contact Admin'
                                                ErrorCondition
                                End  

                 exec sp2

commit transaction

End Try

BEGIN CATCH
      

  IF XACT_STATE() <> 0  
     ROLLBACK    
     Select  Error_message() ErrorCondition
END CATCH           

sp2 code ( not enclosed within begin and commit transaction and try catch block)




my problem is that if the condition is not met only sp1 is getting rolled back  and sp2 is getting executed.  

In one of previous post i was told that once the sp2 is executed with in the Begin transaction of the sp1 , sp2 is implicitely part of sp1 transaction.

can some expert help me to resolve the issue
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros