Transaction Handling in Insert Update Store procedure in SQL Server
If we have more than one table insert or updates logic and transaction is not handled in the front end side and any exception raise then how manually we have to fix the issue to avoid that issue we have to use the proper transaction handling.
Below code will help you to use the Transaction in try catch block:
BEGIN TRY
BEGIN TRANSACTION
--Place the Insert update logice
---
COMMIT TRANSACTION
END TRY
BEGIN CATCH
--Roll back the transaction
ROLLBACK TRANSACTION
--If any error the raise the error
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END CATCH
Hope this will help