Geek asks and answers

If @@ trancount> 0 does not work

I am using SQL Server 2012, and I wrote a small stored procedure with a return 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 try to cancel the transaction, but when I execute this procedure, the rollback operator never executes, I am debugging the procedure and @@ TRANCOUNT is equal to 1. But I still I don’t understand why it doesn’t work. And I found out that we do not need to use start tran and end tran to rollback.

Can someone help me in solving this problem.

EDIT

Sorry to forget to mention this, an error occurs in the second insert statement.

+5
source share
1 answer

. , (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
+5

More articles:


All Articles