SQL Server Transaction Command Template

--
-- Example 1
-- ----------------------------------------------------------------------------
begin try
    begin transaction;
        -- ...
        -- Code goes here
        -- ....
    commit transaction;
end try
begin catch
    if @@trancount > 0
        begin
            rollback transaction;
        end;

    declare @ERROR_SEVERITY int,
            @ERROR_STATE int,
            @ERROR_NUMBER int,
            @ERROR_LINE int,
            @ERROR_MESSAGE nvarchar(4000);

    select @ERROR_SEVERITY = error_severity(),
           @ERROR_STATE = error_state(),
           @ERROR_NUMBER = error_number(),
           @ERROR_LINE = error_line(),
           @ERROR_MESSAGE = error_message();

    raiserror('Msg %d, Line %d, :%s', @ERROR_SEVERITY, @ERROR_STATE, @ERROR_NUMBER, @ERROR_LINE, @ERROR_MESSAGE);
end catch;


--
-- Example 2
-- Source: https://github.com/readyroll/sql-server-samples/blob/master/samples/databases/wide-world-importers/readyroll/WideWorldImporters/Programmable%20Objects/Website/Stored%20Procedures/InsertCustomerOrders.sql
-- ----------------------------------------------------------------------------

create procedure Website.InsertCustomerOrders
    @OrdersCreatedByPersonID int,
    @SalespersonPersonID int
with execute as owner
as
begin
    set nocount on;
    set xact_abort on;

    begin try
        begin tran;
            -- ...
            -- Code goes here
            -- ....
        commit;
    end try

    begin catch
        if xact_state() <> 0
            begin
                rollback tran;
            end;
        print N'Unable to create the customer orders.';
        throw;
        return -1;
    end catch;

    return 0;
end;