Transaction Handling in Insert Update Store Procedure in SQL Server

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