If @@Trancount > 0 is not working

rollbacksql-server-2012transactions

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.

Best Answer

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
Related Topic