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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With