mismatching number of BEGIN and COMMIT statements (T_SQL) [modified]

[Naunt]

Dear all, Please kindly advise this, my storeprocedure structure is as below. The purpose is, While Insert in TableA if there is any error occour Then I want to ROLLBACK TRAN of TableB that related with the current Transaction of TableA, if not COMMIT TRAN. When execute it I have got the error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1159.

DECLARE Cur_1 Cursor LOCAL FOR
Select col1,col2,col3 from table1
OPEN Cur_1
FETCH NEXT FROM Cur_1
INTO @col1,@col2,@col3

WHILE @@FETCH\_STATUS = 0 
BEGIN
		
    DELETE FROM FROM TableA Where match with @col1,@col2,@col3
    DELETE FROM FROM TableB Where match with @col1,@col2,@col3

        DECLARE Cur\_2 LOCAL FOR		
     Select \* from table2 Where match with @col1,@col2,@col3
	 OPEN Cur\_2
	 FETCH NEXT FROM  Cur\_2

	 WHILE @@FETCH\_STATUS = 0 
	 BEGIN

BEGIN TRAN
INSERT INTO TableB (data from Cur_2 & Cur_1)

	    FETCH NEXT FROM  Cur\_2
	 END
	 CLOSE Cur\_2
	 DEALLOCATE Cur\_2					
		
    INSERT INTO TableA (data fromCur\_1)
		
    IF @@ERROR = 0
	COMMIT TRAN
    ELSE
	ROLLBACK TRAN

    FETCH NEXT FROM  Cur\_1
	INTO @col1,@col2,@col3
END

CLOSE	Cur\_1
DEALLOCATE Cur\_1

modified on Friday, September 2, 2011 12:35 AM

[Geoff Williams]

Your problem is that your BEGIN TRAN statement is inside a WHILE loop and hence it will be executed every time the loop code is executed. You need to move it outside the loop.

[Lost User]

To add to Geoff's answer, it is always a best programming practise to enclose transactions inside a Try Catch block, like this:

BEGIN TRY

BEGIN TRANSACTION

-- DO YOUR STUFF HERE

COMMIT TRANSACTION

END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
DECLARE @ErrMsg VARCHAR(8000), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg,@ErrSeverity,1)
END CATCH

[Ganu Sharma]

try something like this. start your transaction begin transaction a insert into tableA if(@@rowcount > 0) begin insert into tableB commit transaction a end else (@@error > 0) begin rollback transaction a end