if @@Trancount > 0 is not working

Question

I'm using SQL Server 2012 and I wrote a small stored procedure with rollback transaction. My procedure is as follows:

ALTER PROCEDURE [dbo].[uspInsertEmployee] 
@EmpId int,
@EmployeeName varchar(50),
@DeptId int
AS
BEGIN
BEGIN TRY

insert into Departments values (@DeptId, 'Testing 1');
insert into Employees values (@EmpId, @EmployeeName, @DeptId);

END TRY
BEGIN CATCH

--log error here
Goto Error_Rollback
END CATCH

Error_Rollback:

IF @@TRANCOUNT > 0
BEGIN
    print 'rolling back transaction' /* <- this is never printed */
    ROLLBACK TRAN
END
END

As you can see, in the If condition, when @@TRANCOUNT > 0, I'm trying to rollback the transaction, but when I execute the procedure, the rollback statement is never executed, I have debugged the procedure and the value of @@TRANCOUNT is 1. But I still do not understand why it is not working. And I got to know that we need not use begin tran and end tran for rollback.

Can anyone help me in solving this issue.

EDIT

Sorry I forgot to mention that, an error occurs in the second insert statement.

score 8 · Accepted Answer · edited Mar 08 '13 at 13:17

8

You've started an implicit transaction. to roll it back, you need to start an explicit transaction (BEGIN TRANSACTION)

ALTER PROCEDURE [dbo].[uspInsertEmployee] 
  @EmpId int,
  @EmployeeName varchar(50),
  @DeptId int
AS

BEGIN

BEGIN TRY
  BEGIN TRAN
  insert into Departments values (@DeptId, 'Testing 1');
  insert into Employees values (@EmpId, @EmployeeName, @DeptId);
  COMMIT TRAN
END TRY

BEGIN CATCH  
  --log error here
 Goto Error_Rollback
END CATCH

Error_Rollback:

  IF @@TRANCOUNT > 0
  BEGIN
    print 'rolling back transaction' /* <- this is never printed */
    ROLLBACK TRAN
  END

END

edited Mar 08 '13 at 13:17

Alex Beynenson

803
11
9

answered Jul 17 '12 at 08:24

Andy Irving

2,657
1
14
11

you mean to say that without explicit transaction, @@trancount value will always be zero rite ? but i while debugging, i saw @@trancount value as 1. but im not using any begin tran and commit tran. – Harsha Jul 17 '12 at 08:57
1

This is an Autocommit transaction and not implicit one, unless SET IMPLICIT_TRANSACTION ON, or SET ANSI DEFAULTS ON, though the answer is correct. – Ghasan غسان Jul 08 '14 at 10:36

if @@Trancount > 0 is not working

1 Answers1