sql server error handling transaction

example for transaction and error handling using sql server stored procedure
-----------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[usp_Example_ErrorHandler]

/* Example stored procedure to illustrate error handling
* Example:
DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
****************************************************************/
AS

DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT

SET NOCOUNT ON

BEGIN TRAN
   INSERT INTO [MOWE_PORTAL].[dbo].[Cities]
           ([CityName]
           ,[CityDesc]
           ,[RegID])
     VALUES
           ('المدينة المنورة','وصف المدينة المنورة',1)

    SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR
    COMMIT TRAN -- No Errors, so go ahead
    RETURN 0
HANDLE_ERROR:
    ROLLBACK TRAN
    RETURN @myERROR