Handling Errors in SQL Server 2012

The THROW statement is available since SQL Server 2012; and combined with the TRY and CATCH blocks, it just makes error handling easier.

create procedure UpdateSales
    @SalesPersonID int,
    @SalesAmt money = 0
as
begin
    begin try
        begin transaction;

        update LastYearSales
        set SalesLastYear = SalesLastYear + @SalesAmt
        where SalesPersonID = @SalesPersonID;

        commit transaction;
    end try
    begin catch
        if @@TRANCOUNT > 0
            begin
                rollback transaction
            end;

        declare @ErrorNumber int = ERROR_NUMBER();
        declare @ErrorLine int = ERROR_LINE();

        print 'Actual error number: '+CAST(@ErrorNumber as varchar(10));
        print 'Actual line number: '+CAST(@ErrorLine as varchar(10));

        throw;
    end catch;
end;
go