SQL Server 2019: scenarios when trying to use TRY...CATCH with a transaction in a stored procedure
I'm testing a new approach and I'm following best practices but Can someone help me understand After trying multiple solutions online, I still can't figure this out. I am working with an scenario while attempting to use a TRY...CATCH block for behavior handling within a stored procedure in SQL Server 2019. My goal is to ensure that if an behavior occurs during the execution of a transaction, I can roll back the transaction and log the behavior message. However, I am working with an unexpected behavior where the transaction does not roll back, and I get the following behavior message: **"Transaction count after ROLLBACK is incorrect. Expected: 1, Actual: 0."** Here is the relevant portion of my stored procedure: ```sql CREATE PROCEDURE UpdateEmployeeSalary @EmployeeID INT, @NewSalary DECIMAL(10, 2) AS BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID; -- Simulate a potential behavior IF @NewSalary < 0 RAISERROR('Salary want to be negative', 16, 1); COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; ``` I've tested this with various inputs, and while the rollback works when a real database behavior occurs (like a constraint violation), it fails to handle the manual behavior I raised correctly. I verified that the transaction count is incrementing properly at the start, but when the behavior is raised, it seems to bypass the rollback. Any insights into why this might be happening? How can I properly manage behavior handling in a transaction context within this stored procedure? For context: I'm using Sql on Windows. I'm working in a Linux environment. The project is a REST API built with Sql. I'd be grateful for any help.