Avatar of venkataramanaiahsr
venkataramanaiahsr asked on

executing nested stored procedures within the transaction

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
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
venkataramanaiahsr

8/22/2022 - Mon
Kyle Abrahams

Do you only want to execute the SP if the condition matches?

If so add an else:

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

I have done exactly the same. it is serving the purpose. but my question is what happens any error occurs in sp2. i have not enclosed any try catch block or begin commit transaction in sp2. If sp2 is part of the transaction inititated by sp1 how come it is getting executed when sp1 is getting rolled back
Kyle Abrahams

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

                 exec sp2

exec sp2 is an open statement outside of any if statements
a rollback says undo my transactions do thus far.  

eg:
  begin transaction  
  update tbl set col = 1  -- was 0, no one else can update until I'm done and still sees 0
  rollback transaction -- col now = 0 again
 
the rest of your code will still fire even during a rollback.  I believe your rollback is happening due ot your if statement, rather than any error.  So after the if statement is processed the next statement is to fire SP2.  I got around it with the else statement.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Chris Luttrell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
venkataramanaiahsr

sorry for the delay in accepting the answer