TSQL: Template for transaction

tsql-transaction-template-rollback-catcherror.sql

BEGIN TRY
    -- Start a transaction
    BEGIN TRANSACTION;
    
    -- Your T-SQL statements go here
    -- Example:
	
	----- YOUR SQL WORK HERE
    
    -- If everything is successful, commit the transaction
    ROLLBACK TRANSACTION; -- COMMIT when ready/stable
END TRY
BEGIN CATCH
    -- An error occurred, roll back the transaction
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    -- Log or re-throw the error
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Optionally, log the error message somewhere or raise an error
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;