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
venkataramanaiahsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
venkataramanaiahsrAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
Chris LuttrellSenior Database ArchitectCommented:
as ged325 is telling you, you have interrupted your Transaction with that ROLLBACK inside your if check.   I usually would have done is something like what I have in the TEST3 block. You can swith the differnt TEST blocks out to experiment with what the BEGIN/ROLLBACK/COMMIT lines are doing with your TRANSACTION.  Just do it a whole block at a time.
-- Test code to prove what is happening inside or outside a transaction
-- creating a table to write into durring test

CREATE TABLE ProofTable
    (
      Id INT IDENTITY
             NOT NULL,
      ActionTime DATETIME DEFAULT GETDATE(),
      WhereAreWe VARCHAR(100)
    );
GO

CREATE PROCEDURE SP2
AS
    BEGIN
        PRINT 'inside sp2'
        INSERT  INTO ProofTable
                ( WhereAreWe )
        VALUES  ( 'inside sp2' );
    END;
GO

CREATE PROCEDURE SP1
AS
    BEGIN TRY
  
        BEGIN TRANSACTION -- begins transaction and sets TRANCOUNT to 1
        SELECT  @@TRANCOUNT TRANCOUNT_after_BEGIN

        DECLARE @TotAmtRefund_Receipt MONEY,
            @TotAmtRefund_BillDet MONEY;
        SET @TotAmtRefund_Receipt = 1.00; -- set these to different amounts to cause ROLLBACK,same amount to see it COMMIT
        SET @TotAmtRefund_BillDet = 2.00;

        INSERT  INTO ProofTable
                ( WhereAreWe
                )
        VALUES  ( 'Inside sp1, should not be in table because it is rolled back'
                );

		---- TEST 1:  *****************************************************************************************************************
		---- basically how you had it and explaining why it behaved the way it did
  --      IF @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
  --          BEGIN
		---- Just a rollback throws another error on the commit, but does not roll back the sp2 results
  --              ROLLBACK; -- rolls back the change and sets TRANCOUNT down 1 to 0
  --              INSERT  INTO ProofTable
  --                      ( WhereAreWe
		--                )
  --              VALUES  ( 'Inside sp1, just ROLLED BACK!'
		--                );
  --              SELECT  @@TRANCOUNT TRANCOUNT_after_ROLLBACK
  --          END
  --      EXEC sp2
		------ END OF TEST 1 *****************************************************************************************************************

		---- TEST 2: *****************************************************************************************************************
		---- Begining a new Transaction and using ELSE with the IF
  --      IF @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
  --          BEGIN
		---- starting a new transaction will avoid the error on COMMIT, but still runs sp2 unless you put it in an ELSE like suggested
  --              ROLLBACK;
  --              INSERT  INTO ProofTable
  --                      ( WhereAreWe
  --                      )
  --              VALUES  ( 'Inside sp1, just ROLLED BACK!'
  --                      );
  --              BEGIN TRANSACTION; -- rolls back the change and sets TRANCOUNT down 1 to 0, then begins a new trans
  --              SELECT  @@TRANCOUNT TRANCOUNT_after_ROLLBACK_new_BEGIN

  --              SELECT  'Mismatch in AmtRefund_Receipt and 
		--									AmtRefund_BillDetail Cannot Refund Bill... Contact Admin' ErrorCondition
  --          END
  --      ELSE
  --          EXEC sp2
		---- END OF TEST 2 *****************************************************************************************************************


		-- TEST 3: *****************************************************************************************************************
		-- how I would have coded it to only have one COMMIT and ROLLBACK controled with TRY/CATCH Block
        IF @TotAmtRefund_Receipt <> @TotAmtRefund_BillDet
            BEGIN

                RAISERROR ( 'Mismatch in AmtRefund_Receipt and AmtRefund_BillDetail Cannot Refund Bill... Contact Admin' 
					, 16, 1);
            END
        EXEC sp2
		-- END OF TEST 3 *****************************************************************************************************************


        COMMIT TRANSACTION; -- Commits the work and sets the TRANCOUNT down 1 (to 0)
        SELECT  @@TRANCOUNT TRANCOUNT_after_COMMIT

    END TRY

    BEGIN CATCH 
      

        IF XACT_STATE() <> 0
            ROLLBACK    
        SELECT  ERROR_MESSAGE() ErrorCondition
    END CATCH;
GO

EXEC sp1

SELECT  *
FROM    ProofTable;

-- Cleaning up the test objects
DROP TABLE dbo.ProofTable;
DROP PROCEDURE dbo.SP1;
DROP PROCEDURE dbo.SP2;

Open in new window

this produces the error message and does not try to run sp2 if the "error" condition is met or they both run and get commited when the values are the same (change the values to test each way).
HTH
Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
venkataramanaiahsrAuthor Commented:
sorry for the delay in accepting the answer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.