SQL SERVER – Handling Error & Transaction

Here is the simple way to handle error and transaction in SQL Server

BEGIN
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN

--INSERT/UPDATE/DELETE statements here

COMMIT TRAN

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN

DECLARE @errMsg VARCHAR(MAX)
SELECT @errMsg = ERROR_MESSAGE()
RAISERROR('Error : %s', 16, 1, @errMsg)

END CATCH
END

This can be helpful to manage transaction as well as error handling in SQL server. We can also keep a log of each error occurred during execution.