IF EXISTS (SELECT * FROM sysobjects WHERE name = N'updatetest' and type = 'U')
 BEGIN
   PRINT 'Dropping table updatetest...'
   DROP TABLE dbo.updatetest
 END
PRINT 'Creating table updatetest...'
GO
CREATE TABLE updatetest 
  (
   id     int PRIMARY KEY, 
   name   varchar(20), 
   amount numeric
  )
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'updatetest' AND type = 'U')
  BEGIN
    PRINT 'TABLE updatetest has been created...'
  END
ELSE
  BEGIN
    PRINT 'TABLE updatetest has NOT been created due to errors...'
  END

GO

insert into updatetest values (1, 'first', 100)
insert into updatetest values (2, 'second', 500)
insert into updatetest values (3, 'third', 30)
insert into updatetest values (4, 'fourth', 200)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'updatevalues'  and type = 'P')
 BEGIN
   PRINT 'Dropping procedure updatevalues...'
   DROP PROCEDURE dbo.updatevalues
 END
PRINT 'Creating procedure updatevalues...'
GO
CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

DECLARE @error int

BEGIN TRANSACTION
IF EXISTS(SELECT * FROM updatetest (XLOCK, SERIALIZABLE) WHERE id = @id)
  BEGIN
    
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id

    IF @@error <> 0 or @@rowcount = 0 
      BEGIN 
        RAISERROR('Failed to update the record', 16, 1)
        ROLLBACK TRANSACTION
        RETURN (1)
      END
  END
ELSE
  BEGIN

    INSERT INTO updatetest VALUES (@id, @name, @amount)
    IF @@error <> 0 
      BEGIN 
        ROLLBACK TRANSACTION
        RETURN (1)
      END

  END
COMMIT TRANSACTION

RETURN(0)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'updatevalues' AND type = 'P')
  BEGIN
    PRINT 'PROCEDURE updatevalues has been created...'
  END
ELSE
  BEGIN
    PRINT 'PROCEDURE updatevalues has NOT been created due to errors...'
  END

GO